Welcome Guest.   Make a donation to an author on the site September 02, 2014, 06:53:13 PM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2014! Download dozens of custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: How Do You Like to Approach Database Design?  (Read 5020 times)
Renegade
Charter Member
***
Posts: 11,206



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« on: March 01, 2008, 09:38:46 AM »

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.
Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
yotta
Supporting Member
**
Posts: 50


View Profile Give some DonationCredits to this forum member
« Reply #1 on: March 01, 2008, 10:30:01 AM »

3rd normal form??
Logged
iphigenie
Supporting Member
**
Posts: 1,166


curiosity FTW!

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #2 on: March 01, 2008, 10:34:45 AM »

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
Logged
iphigenie
Supporting Member
**
Posts: 1,166


curiosity FTW!

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: March 01, 2008, 10:42:48 AM »

Quote
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.  undecided

I'm sure people taking courses at the moment have it clearer in their head.
« Last Edit: March 01, 2008, 10:54:22 AM by iphigenie » Logged
Renegade
Charter Member
***
Posts: 11,206



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #4 on: March 01, 2008, 12:49:09 PM »

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

Quote
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?
Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
Renegade
Charter Member
***
Posts: 11,206



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #5 on: March 01, 2008, 12:54:46 PM »

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.
Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
urlwolf
Charter Member
***
Posts: 1,784



see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #6 on: March 02, 2008, 09:17:44 AM »

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?
Logged
Renegade
Charter Member
***
Posts: 11,206



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #7 on: March 18, 2008, 06:32:10 AM »

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...
Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
notzippy
Supporting Member
**
Posts: 4

see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #8 on: July 24, 2008, 12:22:07 PM »

You may want to checkout http://www.polderij.nl/happyfish/
Logged
jgpaiva
Global Moderator
*****
Posts: 4,710



Artificial Idiocy

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #9 on: July 24, 2008, 03:12:55 PM »

notzippy: why do you recommend happy fish?
Logged

40hz
Supporting Member
**
Posts: 10,651



see users location on a map View Profile Read user's biography. Give some DonationCredits to this forum member
« Reply #10 on: August 05, 2008, 02:12:22 PM »

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

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.) Cool
« Last Edit: August 05, 2008, 02:30:08 PM by 40hz » Logged

Don't you see? It's turtles all the way down!
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.041s | Server load: 0.15 ]