Slashdot Mirror
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."
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. I happened to learn on Data Modeling Essentials which was decent but not great. I have heard good things about Len Silverston's growing series that concentrates more on patterns. But really what you're going to want is a book on data modeling or analysis that teaches you the orders of normal form, when to use cross reference tables, etc so you can get a better idea of good modeling standards. At a million rows, you might not find the need to refactor if you read about the new best practices but perhaps you could make a business case to eventually migrate.
Now there are other topics that require entirely separate books because they are such a diverging path from relational databases. It's not common but your database can be based on something other than an object or table. If you consider the internals of Google, perhaps BigTable is the most prolific database implementation out there and while interesting, it is sort of a very specific proprietary database implementation. You could take this approach to tailor your company's database to be precisely what you need but this would clearly be overkill in your case. You don't talk about any bottlenecks or impending loads that need to be carefully considered so instead of treading down this path, I suggest you first take a course on MySQL or get the de facto book on whatever database you use and play around with fine tuning on a test system. A lot of DBs out there allow you to tune them through a configuration file so that your particular needs are met more closely. If you're looking for this sort of continuing education just out of curiosity, pick up a book on database design and start to tinker. But it requires a lot of knowledge and effort to start a database technology from scratch and compete with vanilla out of the box technologies like MySQL and PostgreSQL.
From what information you provide in your question, I'd suggest this book to help you understand database designs more via industry proven patterns. That assumes you have all the basic database design practices covered.
My work here is dung.
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!
Well, it depends on the size of the company does it not? Perhaps they employ fifteen to twenty staff with an IT department of 2 or 3, mostly focused on hardware and user support. Then it would be much more reasonable for the Director of IT to be a coder who is also taking management responsibility.
You're right that if the company grows, management should be the focus and a decent DBA employed, but until then like many small companies the poster may have to be a jack of all trades. At least they're showing incentive in seeking to master at least one of their areas of responsibility.
O'Reilly books are your friend. The "... in a Nutshell" books are a good place to start, and then proceed into the more advanced books. They have 25 titles related to MySQL and 53 titles related to Microsoft SQL. There are usually a few to browse through at the large chain book stores.
Serious? Seriousness is well above my pay grade.
.. either that or he's the only programmer in the company and can thus effectively call himself whatever he wants.
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.
I'm a bit unclear about what you want to achieve:
- easier end-user interface
- more reliability (backups, journalling, redundancy...)
- more speed
- more security
- more complicated data massaging (multi tables, statistics...)
- better vizualization (reports, graphs...)
I'm not sure a single book can cover all that.
The Cloud - because you don't care if your apps and data are up in the air.
He was asking for a book, not your stupid criticism.
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.
"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."
There is more to RDBMS than tables and SQL. Your developers should understand data normalization first and foremost, at least 1NF, 2NF and 3NF.
http://en.wikipedia.org/wiki/Database_normalization
http://en.wikipedia.org/wiki/First_normal_form
http://en.wikipedia.org/wiki/Second_normal_form
http://en.wikipedia.org/wiki/Third_normal_form
The examples in the URLs above should suffice for getting a general understanding on how to start with a relational model. As for books, I'd suggest these:
http://www.amazon.com/Relational-Database-Design-Implementation-Third/dp/0123747309/ref=sr_1_4?ie=UTF8&s=books&qid=1278630155&sr=8-4
http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688/ref=sr_1_3?ie=UTF8&s=books&qid=1278630306&sr=1-3
I would also suggest C.J. Date's "Database in Depth: Relational Theory for Practitioners", but I can imagine the local penny arcade l33t-hax0r-wannabe crowd going batshit crazy about studying relational algebra and relational database theory in depth. To each his own. Most problems that arise in poorly designed relational database models arise from not understanding data normalization
:
HTH.
I did an exam on SQL and database design recently and used The Manga Guide to Databases as part of my studies. If you don't want something too rigorous it's very good indeed - I found it a lot better at making stuff sink in than a dry, stuffy book. It gives a reasonably good idea of things like the first, second and third normal forms. Don't be put off by the fact that it looks a bit childish - the storytelling idea really works well. It probably won't work for everyone, but it did work well for me (I passed the exam with flying colours).
Do you know relational algebra? If you don't, then I highly recommend:
Codd, E.F. (1990). The Relational Model for Database Management (Version 2 ed.). Addison Wesley Publishing Company. ISBN 0-201-14192-2.
It's MUCH better to know the fundamentals of database systems and then try to figure out details than vice-versa.http://ask.slashdot.org/story/10/07/08/2142211/Good-Database-Design-Books?art_pos=1#
...and improve your quality and maintainability?
Back in the 70's and early 80's we learned a methodology called, "Data Structured Systems Design" and the fundamental presupposition was that everything could be expressed logically and accurately by describing it as relationships in set theory. I have not seen anything since that surpasses the quality and maintainability of database applications and systems.
Someone already mentioned Joe Celko's book "SQL for Smarties" and I would recommend you first read his, "Thinking in Sets" before any of his other books.
I would also suggest some earlier books by Ken Orr and Jean Dominique Warnier. If you learn the Warnier-Orr approach to DESIGNING the system before doing any coding, you will reduce the time necessary for maintaining the system. I have seen hundreds of small IT shops like yours, and much of the time Systems Analysis and Design is neglected and performed "off-the-cuff" by programmers who can't wait to get to the coding. I didn't originally believe Ken Orr's assertion that spending twice as much time designing the system would result in a sharp time reduction for overall project completion, but through experience and observation I became a believer.
"The mind works quicker than you think!"
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.
"Knowing everything doesn't help..."
At very least you need an understanding of what normalisation gives you, particularly in terms of ensuring data consistency, before you should consider denormalization. I see all to many database "designers" bad-mouthing normalisation when i suspect they simply can't be bothered to normalise, so they go on not to bother thinking about data consistency either. Yes, there are cases where denormalization is an advantage, but there are a lot more cases where denormalization is laziness that leads to database problems.
Quidnam Latine loqui modo coepi?
He's lame then.
If I had the flexibility to call myself whatever I wanted, I'd make damn sure that my business cards all said, "Batman" on them.
Non impediti ratione cogitationus.
The fact that he's asking Slashdot tells me that he's not comfortable letting someone else do the work, possibly because he's Superprogrammer and always knows what's best.
The fact that he's asking Slashdot tells me he's willing to listen to the lunatic ravings of people on Slashdot (such as myself), indicating that he's aware that he doesn't know best. If he thought he knew best, he wouldn't ask.
Our CIO has a programming background and once fixed some database code we were having problems with. This is a 10,000 person organization with an IT staff of around 300. It's not hard to imagine a small company where the IT director takes on some programming tasks.
In the modern days of cheap disk, big disk caches, and large ram, proper modelling is more important than strict normalization.
Back when those books were written, disk was expensive and not cached, RAM was very expensive, and machines had terrible I/O bottlenecks.. Normalization is critical under these circumstances for maximum performance.
Today, these normalization techniques will increase performance but not as much as you might think. Really it is best to concentrate efforts elsewhere, especially for a one-person shop.
All of that normalization work requires coding changes and it will undoubtedly make the code much less readable and maintainable.
<facepalm/> Performance? I wasn't even thinking of that as a reason to understand normalization. I'm thinking data integrity at least in the conceptual model.
The value of normalization is not so much in performance but in considering and planning what a decent data/information model should look like.
You normalize your *model*, your blueprint, and de-normalize as you see fit with the resources available. The actual tables and tablespaces might not (will not) look 1-to-1 to the model, but you still have a model of information.
And when you do deviate from your model, you do so consciously; you know exactly where you are deviating from the model; and you know why. Without a model, or worse, with a badly crapped model, you don't know what you have.
It is good to exploit the hardware capabilities we have now, BUT without at least having a conceptual understanding of normalization, this is almost always a sure way to get into a corner where the only option is to throw more hardware to the problem.
The distinction here now is that people deploy hardware strategically, but because they have no choice: shit won't run without. Hardware is cheap. Operational costs are not. Understanding normalization is to (relational) data modeling and building what modularity and structure are to OO design (and software building in general.)
Normalization is not about performance (even if its immediate effects in the past were performance related.) It is about reduction of unnecessary data redundancy that compromises data integrity.
Not performance. Data Integrity.
Even with the hardware that we have today, I still have to see a well-designed model that does not in great part enforce the 2nd normal form and most attributes of 1st normal form (in particular about avoiding duplication of rows and maintaining regular columns.)
Back when those books were written, disk was expensive and not cached, RAM was very expensive, and machines had terrible I/O bottlenecks. Normalization is critical under these circumstances for maximum performance.
Normalization has _nothing_ to do with performance. In relational DB design, performance is usually considered only after you have a normalized model at which point you it's common to denormalize for performance and other implementation-specific reasons.
The parent's first link gives a good description of the purposes of normalization.
Today, these normalization techniques will increase performance but not as much as you might think. Really it is best to concentrate efforts elsewhere, especially for a one-person shop.
As the submitter looks to be using RDBMSs, a knowledge of normalization and relational database design should be required I 'd have thought. However, if their systems were designed around ORDBMS (your posts hint that this is your background) the DB design issues would be different, but the summary doesn't suggest this is the case.
Database fundamentals haven't changed much. I don't know how much you know so far but this guy is pretty smart:
http://philip.greenspun.com/sql/
http://philip.greenspun.com/panda/
http://philip.greenspun.com/wtr/
Lots of the core stuff about RDBMSs goes back decades and even old stuff like this is still very relevant. Try reading this page (just a dozen printed pages) and see what you think. He covers a lot of the fundamentals well and his style of writing is pretty entertaining.
Dear Slashdot: next time you want to mess with the site, add a rich-text editor for comments.