A beginner's guide to SQL database design [closed]

I started with this book: Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jan L. Harrington and found it very clear and helpful

and as you get up to speed this one was good too Database Systems: A Practical Approach to Design, Implementation and Management (International Computer Science Series) (Paperback)

I think SQL and database design are different (but complementary) skills.


I started out with this article

http://en.tekstenuitleg.net/articles/software/database-design-tutorial/intro.html

It's pretty concise compared to reading an entire book and it explains the basics of database design (normalization, types of relationships) very well.


Experience counts for a lot, but in terms of table design you can learn a lot from how ORMs like Hibernate and Grails operate to see why they do things. In addition:

  1. Keep different types of data separate - don't store addresses in your order table, link to an address in a separate addresses table, for example.

  2. I personally like having an integer or long surrogate key on each table (that holds data, not those that link different tables together, e,g., m:n relationships) that is the primary key.

  3. I also like having a created and modified timestamp column.

  4. Ensure that every column that you do "where column = val" in any query has an index. Maybe not the most perfect index in the world for the data type, but at least an index.

  5. Set up your foreign keys. Also set up ON DELETE and ON MODIFY rules where relevant, to either cascade or set null, depending on your object structure (so you only need to delete once at the 'head' of your object tree, and all that object's sub-objects get removed automatically).

  6. If you want to modularise your code, you might want to modularise your DB schema - e.g., this is the "customers" area, this is the "orders" area, and this is the "products" area, and use join/link tables between them, even if they're 1:n relations, and maybe duplicate the important information (i.e., duplicate the product name, code, price into your order_details table). Read up on normalisation.

  7. Someone else will recommend exactly the opposite for some or all of the above :p - never one true way to do some things eh!