Understanding SQL REFERENCES

Question

Recently I've asked a question about the best way to go to design a DB schema to support multiple types of users and interactions between them, one of the answers suggested that I use one table for each user type and Distributed Keys.

The thing is the only databases I actively work with are MySQL and SQLite and I've always done this kinda of work of maintaining the integrity of the DB on the programming side and never directly in the database, can someone point me to a detailed yet easy to understand guide on foreign keys, references and related subjects?

Thanks in advance!

EDIT: I'm interested specifically in MySQL usage examples and documentation, I've already searched in the MySQL manual but nothing useful comes up.

Answer 1

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

Specifically related to MySQL:

• Referential Integrity in MySQL
• Foreign Keys and Referential Integrity

Answer 2

try these:

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

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

Answer 3

If you like to read books, try Beginning Database Design: From Novice to Professional by Clare Churcher. You can take a look it at google books.

Answer 4

This isn;t mysql specific, but there is some good stuff in here http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/

I don't agree with him about the use of natural keys vice surrogate keys. I have found surrogate keys in general work better for primary keys, but if you have a natural key you should put a unique index on it to prevent duplication of data. Pay particular attention to the sections on:

Not using SQL facilities to protect data integrity

Trying to code generic T-SQL objects

One table to hold all domain values

Another good starting place is: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Answer 5

Hugh Darwen has made his course on Relational Algebra/Database Technology publicly and freely available. Search for "An Introduction to Relational Database Theory" on www.thethirdmanifesto.com.

It's introductory, so nothing "advanced", but at least you won't be told anything that is an outright violation of the theory.