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

Question

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!

I really liked this article.. http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

Head First SQL is a great introduction.

These are questions which, in my opionion, requires different knowledge from different domains.

1. You just can't know in advance "which" tables to build, you have to know the problem you have to solve and design the schema accordingly;
2. This is a mix of database design decision and your database vendor custom capabilities (ie. you should check the documentation of your (r)dbms and eventually learn some "tips & tricks" for scaling), also the configuration of your dbms is crucial for scaling (replication, data partitioning and so on);
3. again, almost every rdbms comes with a particular "dialect" of the SQL language, so if you want efficient queries you have to learn that particular dialect --btw. much probably write elegant query which are also efficient is a big deal: elegance and efficiency are frequently conflicting goals--

That said, maybe you want to read some books, personally I've used this book in my datbase university course (and found a decent one, but I've not read other books in this field, so my advice is to check out for some good books in database design).