Good Database Design Books?

OneC0de writes "I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications. We use a variety of technology at our office, the majority of which rely on MS-SQL and MySQL databases. I am familiar with tables, SQL queries, and have a general understanding of how the SQL databases work. What I'm looking for is a good book, particularly a newer book, to explain general database design techniques, and maybe explain some relational tables. We have some tables that have million of rows, and I'd like to know the best method of designing these tables."

Comments

Database in Depth

[Anonymous Coward]

Database in Depth: Relational Theory for Practitioners
Publisher: O'Reilly Media; 1 edition (May 1, 2005)
Language: English
ISBN-10: 0596100124
ISBN-13: 978-0596100124

Best DB book i have ever owned/read/seen!

Re:Somewhere, a coder is polishing his resume

[OneC0de]

I'm the article poster. Our company is relatively small, with an IT staff of less than 5, and total company size less than 50. I write all the code, simply because none of our other IT pros are comfortable enough writing it. If there were "coders" under me to ask, please believe I would use them as a resource first.

Re:A Few Suggestions

[RobertM1968]

We have some tables that have million of rows, and I'd like to know the best method of designing these tables.

I'm a developer, not a database expert. But it seems that every now and then I have to get my hands dirty with data modeling. "The best method" is probably a really vague concept. If you have serious hardware constraints than the best method changes from an easily maintainable system to something more complex. There's give and take in database design and I guess a million rows is really something that a traditional relational database should be able to handle. So I'd suggest any book that teaches data modeling will suit you here.

eldavojohn makes some excellent points and gives some great suggestions. Keep in mind, like elda suggests, nothing is cut and dry. Configuration, resources, numbers of connections for specific data, etc; all will have an impact (or should) on what you should do and how you should design.

Re:A Few Suggestions

[hguorbray]

http://www.amazon.com/Case-Method-Entity-Relationship-Modelling/dp/0201416964

I used this book at Foothill college in an intro to data management class and it taught me more than any of the dozen oracle classes I took once I got past the terminology of tuples, etc

this one is also well-recommended:
http://www.amazon.com/Database-Systems-Design-Implementation-Management/dp/0760049041

and this one is good for people without dba or architect background:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=sr_1_1?ie=UTF8&s=books&qid=1278629171&sr=1-1

I would stay away from the vendor specific books as good database design s/b dbms agnostic

-I'm just sayin'

Good SQL design books:

[8282now]

IMHO: Joe Celko's SQL for Smarties (http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/0123693799/ref=sr_1_2?ie=UTF8&s=books) has shown itself to be very nice book when the need to go beyond the basics to a little deeper understanding of SQL is needed.

There are many other books on the subject all the way to source material from Date and Dodd but Celko seems to be well informed and writes fairly well, I think.

Three practical lessons

[Invisible Now]

These three lessons may not all be in any one book, but they can help in the real world:

1) Learn what SQL Injection is and how to defend against it. It will ruin your day and could severely damage your current employment situation.

2) Abstract your schema from your front-end applications. Stored procedures are easy to write and can provide security and if well written stop injection attacks. They will let you change your database design without breaking your deployed apps. Just update the internal code in the P. Middleware and objects can do this, too.

3) Bergstrom's law of sailing says: "You can get away with anything in less than 5 knots of wind." Similarly, any little box or blade with 2 to 4 gs of RAM can easily handle 5 to 10 million row tables. Dedicate the server to MySQL or MS SQL so they can cache and buffer efficiently and they will outperform much bigger boxes trying to run too many schemas and DBs concurrently. Learn to index. Don't be too puritanical about normalization. Returning a customer address should require 6 joins. And remember that moving that moving large recordsets across the LANWAN may take much more time than the server query.

You probably already know all this... but maybe someone else reading this doesn't.