DataZulu

The presentation given yesterday at SQL Saturday Portland is up on the download page. Or directly at this link.

MS SQL Server, Database Design, Presentations presentation, code-sample, xml, relational

What fun it was presenting at SQL Saturday #5. Officially the first time I've presented at a technical conference. We had a great turnout and the event planners did a great job putting the whole thing together. Thanks to those who attended and for the great questions you asked!

Grab the slides and code for the presentation here.

(If link broken, just navigate over to the download page.)

MS SQL Server, Database Design, Presentations presentation, code-sample, xml, relational

This is an ordered list (not a relation!) of my reading recommendations that emphasize relational fundamentals and building upon that to create great database models and implementations. Many other specific recommendations are scattered throughout the Training Slides (in the notes), for those who have copies of them.

1. C.J. Date, Database In Depth: Relational Theory for Practitioners. O'Reilly Media: 2005. ISBN: 0-596-10012-4. (Amazon link.)

This should be required reading for anyone who is a database professional. This really is a "greatest hits" of the core concepts Date has been espousing for years and which too few people really understand. If you read only one "theory" book, make it this one!

2. C.J. Date, "What First Normal Form Really Means." (Article)
You can get this directly (for a fee) via www.dbdebunk.com, or as Chapter 8 in the recently released compilation of Chris's writings (see below, #11).

This has given me more comprehension, per page of reading, about the relational model than any other thing I have read. It is so much easier to understand this one concept deeply, once, than to read and re-read the pithy two-paragraph spouting found in most modeling books about "eliminating repeating groups."

3. Graeme C. Simsion and Graham C. Witt, Data Modeling Essentials (3rd ed.). Morgan Kaufmann: 2004. ISBN: 0-12-644551-6. (Amazon link.)

  This is my favorite data modeling book and has a very pragmatic balance over theory for theory's sake. It also does an excellent job explaining the more advanced normalization issues. (Note, it does not include anything on 6NF.)
  If you have more a coding bias in your database work, I'd put Code Complete (next) higher on the list than this.

4. Steve McConnell, Code Complete (2nd ed.). Microsoft Press: 2004. ISBN: 0-7356-19670. (Amazon link.)

  Simply one of the best software engineering books ever published. This will help you become a better programmer no matter what language you use.
  If you have more a modeling/design bias in your database work, I'd put Data Modeling Essentials (above) higher on your list.

5. Joe Celko, SQL For Smarties: Advanced SQL Programming (3rd ed.). Morgan-Kaufmann: 2005. ISBN: 0123693799. (Amazon link.)

  Celko is the main guy for coding great SQL and solving problems the "SQL way" that might not be otherwise intuitive. I recommend having and referencing 2 or 3 of his books as needed.
  Note, however, he is no expert at pure relational ideas and often contradicts what I think is a mor academically-correct approach. Read Date for "what's right" and read Celko for "how to get the job done."

6. Stuart R. Faulk, "Software Requirements: A Tutorial." In Software Requirements Engineering, 2nd ed., 1997. (Amazon link for the whole book, or just the article can be found here and other places (just do a search).)

Not only an excellent summary of the Requirements problem as a whole, but an excellent article on how to design work-products (like requirements or models) to be useful to the audience.

7. Robin Williams, The Non-Designer's Design Book, 3rd ed. Peachpit Press: 2008. ISBN: 0-321-19385-7. (Not that Robin Williams!) (Amazon link.)

Your models are only as good as they communicate. This book is a great primer for generally making designed things (documents in particular) good without going through college as a graphic designer. I think anyone who produces materials for others to consume should read this. It's pretty short and fun.

8. Hugh Darwin, "The Askew Wall" (presentation). (Available here.)

Darwin walks through just some of the most egregious problems with the SQL language. Covers both non-relational aspects of SQL as well as just language design issues.

The rest of the list . . .

. . . are better for a team reference library and shared as needed. Most are a read-once, or an occasional reference, but very useful.

9. C.J. Date, The Database Relational Model: A Retrospective Review and Analysis. Addison Wesley: 2001. ISBN: 0-201-61294-1. (Amazon link.)

Reading this short book is probably more useful than reading Codd's original papers. Chris explains those two seminal papers and comments on areas that have changed (few!) and how they stand up to today's criticisms.

10. C.J. Date, An Introduction to Database Systems, 8th ed. Addison-Wesley: 2004. ISBN: 0321197844. (Amazon link.)

This is a de-facto text book for advanced undergraduate or graduate level database courses and is really what put Chris Date "on the map" for his expertise and clear writing. The 8th edition is worth having if for no other reason than Appendix A which shows by example some of why the "Transrelational Model" could be so revolutionary in implementing truly relational products.

11. C.J. Date, Date on Database: Writings 2000-2006. Apress: 2006. ISBN: 1-59059-746-X. (Amazon link.)

  Chris's shorter writings (articles, whitepapers) become available occasionally in collections like this one. Buying this one book gives you "What First Normal Form Really Means" (#2 above) in Chapter 8, and some great retrospective on Ted Codd in Chapter 1, among many other great topics.
  After purchase, you can spend an extra $10 to get the downloadable PDF version of the book from Apress.

12. Len Silverston, The Data Model Resource Book. (2 volumes.) Wiley: 2001. ISBN (vol. 1): 0-471-38023-7, (vol. 2): 0-471-35378-5. (Amazon links to Volume 1 and Volume 2.)

Len's is the best compilation of ready-made database designs covering various common business scenarios (Volume 1) and some specific industries (Volume 2). I don't advocate using the designs verbatim, but they are a great reference and idea source. It's useful to cross-check your own designs with ones this veteran of the field has assembled as a sanity check.

Database, Database Design, Software Engineering relational, sql, modeling, books

If supporting foreseeable business changes requires adding tables or columns to your database, the design is deficient.

Over the years I have learned many principles regarding database design. Some I discovered on my own, usually through my own mistakes and pain. Some passed to me through a mentor or seminar. But the very first of all these is still in my Top Ten list of database design ideas:

If supporting foreseeable business changes requires changing the structure of your database, you have a problem with the design.

The database design should not have to change when obvious expected things happen. Things like adding a new customer, or updating a product's price, or . . . well, August.

This nugget came to me from a coworker in my first position where I dealt with databases although I don't think he realized it at the time. I had recently migrated an Excel-centric department reporting application over to Microsoft Access 2.0. I gained a bit of notoriety in the division as having some expertise with Microsoft Office in general and others frequently asked for tips or help troubleshooting.

So a co-worker asks me one day, "Eric, is there a limit to the number of tables you can have in a UNION query?"

"Um..."

I didn't know! At first I was somewhat flustered that I didn't know. But then I also couldn't think of a scenario where it would matter too much. So I responded:

"Why do you ask?"

He drew my attention to his monitor where he pointed at the tables in his database, a work-in-progress. They were:

As dense as I can be, I could even see where this was going.

So I introduced him to my friend, the "date" type and showed how, with a simple yet magical column called "EffectiveMonth," one big table can actually store all these results and you can just flag each one by what month the results were for.

"Oh." He paused, bewildered. "Wow, that's neat!"

Indeed it is.

I have since come to believe this principle is one of the clearest marks of good database design. Or good software design in general. Think about likely changes and build a structure that won't make the obvious changes a nightmare to handle.

A "senior" developer I worked with more recently in my career was fond of saying, "You can't anticipate future requirements." I argue instead: You must anticipate future requirements if you call yourself a professional. Otherwise, you are just a typist who knows some syntax.

(As an aside, check out what has to be the worst example of this I have seen to date.)

Database Design abstraction, normalization, axioms