How Do You Like to Approach Database Design?

[Renegade]

Spinning off from another thread...

So? What do you like to do? Hate to see? Scream/cry when you see?

I like to a bit beyond 3rd normal form for a lot of things. I'm certainly not shy about spinning off data into a 1:1 identity relationship to ease the load on a table and keep less "immediate" data in another table (or to keep the table small -- they have limits for row size).

One thing that makes me scream inside is when I see structures that are below the 3rd normal form.

Another thing that makes me scream inside (and sometimes outside) is seeing tables with extra columns to store derived data in a parent/child relationship that needs to be controlled in code, when the simple and safe solution is to relate the table to itself with a foreign key to indicate the parent in the table.

Basically, any time I see a design where things need to be controlled inside of code, and not in the database, a little piece of me dies...

I do like joining tables on themselves for parent/child relationships. I don't really like it much for anything beyond a single join, but I'd go for 2 or 3 if I know that the usage won't be a problem and that it won't go beyond that.

I suppose that whenever possible, I like off-loading the work to the database.

[yotta]

3rd normal form??

[iphigenie]

I agree with you that many things are better offloaded to the database.

I am often apalled in web development how little is offloaded to the database. I think it is the fault of the way people learned, often learning database abstraction layers and learning on databases like mysql which until recently did not have foreign keys, subqueries or other basic tools of the DBA trade.

My favorite databases has been Postgres in recent years, it's a beauty to develop for/with - although I hate the absence of easy, simple replication

[iphigenie]

3rd normal form??

It is about the steps you take to "clean" your database ideas - the 5 housekeeping checks to get a "clean" database. They're more mathematical than that.

IIRC the 1st normal form is about not having repeating columns and having an unique key (single or composite) in each table

the second normal form is about splitting off things that belong together into separate related tables (i.e. taking the product information outside of the shopping cart or separating address from personal details), making sure all your information is directly related to the key. This is about breaking down your content and index into meanigful data sub-chunks

The 3rd normal form is that the data in my table that actually exists "outside" this table's context should be outside it, this is where it really gets relational. Here you hunt down columns that dont belong in one table but should be separated...

Theres a few more steps after that that get more and more mathematical

Once you reach your clean database you often have to start "breaking" the cleanness for performance reason, using views or cache tables etc. 

I'm sure people taking courses at the moment have it clearer in their head.

[Renegade]

Third Normal Form

Fourth Normal Form -- This is what I aim at as a minimum in general. Which generally satisfies:

Fifth Normal Form

There is also:

Sixth Normal Form

Not useful for most people though.

Another important concept: Denormalization

I agree with you that many things are better offloaded to the database.

I am often apalled in web development how little is offloaded to the database. I think it is the fault of the way people learned, often learning database abstraction layers and learning on databases like mysql which until recently did not have foreign keys, subqueries or other basic tools of the DBA trade.

My favorite databases has been Postgres in recent years, it's a beauty to develop for/with - although I hate the absence of easy, simple replication

It really is a terrible sin.

If you have a bug, you then corrupt the data in your database. The cost to fix it is immense. Say you have 20,000 records (a small table) and you know that at least 50 have been poisoned by some bad logic in code... OUCH! Database == haystack. Bad record == needle. Not fun.

In the past I would never ever have touched MySQL simply because it wasn't a real RDBMS and hence couldn't be trusted.

I fiddled with PostgreSQL a number of years ago, but it's just easier for me to deal with MS SQL Server whenever possible. And I never understood why anyone would use MySQL when they had PostgreSQL available. That completely baffled me. RDBMS vs. huh?

[Renegade]

Oh, I should mention my thoughts on the new DeZign ERD tool. Very handy program that I've been using since 2002 or so. (Forget exactly when I bought it -- around then anyways.) Just upgraded to v5 though.

[urlwolf]

Thanks Renegade for your blog post.
I checked the prices (195-595 eur!). That's really expensive, and other than the colorting, I didn't see anything that I couldn't do with fabForce dbdesigner (OSS). Of course this is just a first impression, I may be missing the main features of DeZign. Have you used fabForce? What's different?

[Renegade]

Thanks Renegade for your blog post.
I checked the prices (195-595 eur!). That's really expensive, and other than the colorting, I didn't see anything that I couldn't do with fabForce dbdesigner (OSS). Of course this is just a first impression, I may be missing the main features of DeZign. Have you used fabForce? What's different?

I will get back to this...

The short answer is that the fabForce was just too difficult to use -- It wouldn't let me select MS SQL Server as the target database, so I immediately ruled it out as a possible free replacement for DeZign.

I look at it this way, if the problem can be solved quickly for cash, then figure out if the time you save is worth it. And really, even for the Enterprise version at 600, it's a massive time saver. Albeit, if a free one will do the job as well, then sure, that's sometimes a better solution.

I'll have another look at it though. I'll need a week or 2 though. Got a lot on my plate for the moment...

[notzippy]

You may want to checkout http://www.polderij.nl/happyfish/

[jgpaiva]

notzippy: why do you recommend happy fish?

[40hz]

(Note: If you're primarily doing web database development, some of what follows will not apply. )

I model the complete input/out stages before I'll even start to build tables and indexes or create code. I try to have mockups of all the user screens and faked samples of all required reports.

Ages ago I went to a presentation by Ed Yourdon, who strongly suggested you spend something like 50% of your development time getting the inputs and outputs straight. He maintained that a database app just about wrote itself once you completely understood what you wanted going in and out. He said something along the line of design being the name of the game, and all the remaining stuff being mostly data plumbing and code craftsmanship. I found that approach worked best for me.

Then there's some psychology. I know it sounds old-fashioned in this era of RAD tools, but I find the longer I put off committing to code, the better my databases tend to be. I think (for me anyway) there's a tendency to always try to rework something once it's coded rather than just completely chuck it and start with a blank edit screen should you figure out a better way. I find that's especially true once the tables are populated with test data.

I look at it this way, if the problem can be solved quickly for cash, then figure out if the time you save is worth it.

I agree with that 100%

"It's not a real problem if you can buy your way out of it."
(Don't remember where I first heard that. But it's true.)