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