Are you aware a great source to learn to design SQL solutions?
Past the fundamental language syntax, I am searching for something that helped me to understand:
- What tables to construct and just how to link them
- How you can design for various scales (small client Application to some huge distributed website)
- Crafting effective / efficient / elegant SQL queries
I began with this particular book: Relational Database Design Clearly Described (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jan L. Harrington and located it very obvious and useful
and as you become up to date that one was good too Database Systems: An Operating Method of Design, Implementation and Management (Worldwide Computer Science Series) (Paperback)
I believe SQL and database design are different (but complementary) abilities.
Experience counts for much, but when it comes to table design you can study so much from how ORMs like Hibernate and Grails operate to determine why they are doing things. Additionally:
Keep various kinds of data separate - don't store addresses inside your order table, connect to a previous address inside a separate addresses table, for instance.
Personally, i like getting an integer or lengthy surrogate key on each table (that holds data, not individuals that link different tables together, e,g., m:n associations) that's the main key.
I additionally like getting a produced and modified timestamp column.
Make sure that every column that you simply do "where column = val" in almost any query comes with an index. Not the perfect index on the planet for that data type, but a minimum of a catalog.
Setup your foreign secrets. Also set on Remove as well as on MODIFY rules where relevant, either to cascade or set null, based on your object structure (which means you only have to remove once in the 'head' of the object tree, and all sorts of that object's sub-objects get removed instantly).
If you wish to modularise your code, you might like to modularise your DB schema - e.g., this is actually the "clients" area, this is actually the "orders" area, and this is actually the "items" area, and employ join/link tables together, even when they are 1:n relations, and perhaps duplicate the key information (i.e., duplicate the merchandise title, code, cost to your order_particulars table). Educate yourself on normalisation.
Another person will recommend precisely the opposite for many or the suggestions above :p - never one true method of doing several things right!
Mind First SQL is a superb introduction.
They are questions which, during my opionion, requires different understanding from different domain names.
- You simply can't know ahead of time "which" tables to construct, you need to know the issue you need to solve and design the schema accordingly
- This can be a mixture of database design decision as well as your database vendor custom abilities (ie. you can examine the documentation of the (r)dbms and finally learn some "tips &lifier methods" for scaling), also the configuration of the dbms is vital for scaling (replication, data partitioning and so forth)
- again, nearly every rdbms has a particular "dialect" from the SQL language, if you want efficient queries you need to learn that one dialect --btw. much most likely write elegant query that are also efficient is really a large deal: elegance and efficiency are often conflicting goals--
Nevertheless, maybe you need to read some books, personally I have used this book during my datbase college course (and located a good one, but I have not read other books within this area, so make an effort to to look at for many good books in database design).
It has been some time since i have see clearly (so, I am unsure the amount of it's still relevant), but my recollection is the fact that Joe Celko's SQL for Smarties book provides lots of information on writing elegant, effective, and efficient queries.