Other Software > Developer's Corner

How Do You Like to Approach Database Design?

(1/3) > >>

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??
--- End quote ---

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
--- End quote ---

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?

Navigation

[0] Message Index

[#] Next page