Recently I have requested an issue about the easiest method to visit design a DB schema to aid multiple kinds of customers and interactions together, one of the answers recommended which i play one table for every user type and Distributed Keys.

The truth is the only real databases I positively use are MySQL and SQLite and I have always carried this out kinda of labor of maintaining the integrity from the DB around the programming side rather than directly within the database, can someone point me to some detailed yet clear to see guide on foreign secrets, references and related subjects?

Thanks ahead of time!

EDIT: I am interested particularly in MySQL usage good examples and documentation, I have already searched in the MySQL manual but nothing helpful pops up.

This is not mysql specific, but there's good quality stuff in here http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

I do not accept him about using natural secrets vice surrogate secrets. I've discovered surrogate secrets generally are more effective for primary secrets, but when you've got a natural key you need to put a distinctive index onto it to avoid duplication of information. Pay particular focus on the sections on:

Not using SQL facilities to safeguard data integrity

Attempting to code generic T-SQL objects

One table to carry all domain values

One other good beginning place is: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Try that one: Relational Database Design Basics or even the Wiki. Give this a read too.

Particularly associated with MySQL:

try these:

http://en.wikipedia.org/wiki/Relational%5Fdatabase <<--has links to articles on Constraints, Foreign secrets, Saved methods, Indices, etc

http://en.wikipedia.org/wiki/Database

If you want to see books, try Beginning Database Design: From Novice to Professional by Clare Churcher. You are able to have a look it at google books.

Hugh Darwen makes his course on Relational Algebra/Database Technology openly and freely available. Look for "Introducing Relational Database Theory" on www.thethirdmanifesto.com.

It's opening, so nothing "advanced", but a minimum of you will not find out anything that's an outright breach from the theory.