Where to learn SQL REFERENCES and foreign keys for schema design?

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.

don't just limit your search to MySQL specific articles. the basic concepts you are after: tables, Indices, Constraints, Foreign keys, etc. exist is just about all relational databases. there may be slightly different syntax when you implement, but when you are learning about what they are, how to use them, and design, the database engine doesn't really matter. — KM., Aug 14 '09 at 15:02

score 2 · Accepted Answer · edited Feb 17 '15 at 15:28

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 versus 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 [dead link Feb 17, 2015]

score 1 · Answer 2 · edited May 23 '17 at 10:32

1

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

Specifically related to MySQL:

Also this stackoverflow question: MYSQL and RDBMS

edited May 23 '17 at 10:32

Community

1
1

answered Aug 14 '09 at 13:57

Mr. Smith

5,489
11
44
60

Perhaps this SO link can help too: http://stackoverflow.com/questions/362839/mysql-and-rdbms – Mr. Smith Aug 14 '09 at 14:19
Added some URLS specifically related to MySQL, hope it helps. – Mr. Smith Aug 14 '09 at 14:26

score 1 · Answer 3 · edited Feb 17 '15 at 15:16

1

Try these:

http://en.wikipedia.org/wiki/Relational_database (has links to articles on Constraints, Foreign keys, Stored procedures, Indices, etc.)
http://en.wikipedia.org/wiki/Database

edited Feb 17 '15 at 15:16

Rob Bednark

25,981
23
80
125

answered Aug 14 '09 at 13:59

KM.

101,727
34
178
212

Thanks, the Foreign Keys link seems to have some useful information. – Alix Axel Aug 14 '09 at 14:19

score 0 · Answer 4 · answered Aug 14 '09 at 14:06

0

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.

answered Aug 14 '09 at 14:06

Whoever

33
1
7

score 0 · Answer 5 · edited Feb 17 '15 at 15:17

0

Hugh Darwen has made his course on Relational Algebra/Database Technology publicly and freely available. Search for "An Introduction to Relational Database Theory" on http://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.

edited Feb 17 '15 at 15:17

Rob Bednark

25,981
23
80
125

answered Sep 15 '09 at 19:14

Where to learn SQL REFERENCES and foreign keys for schema design?

5 Answers5