How do you design your Database Tables?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

How do you design your Database Tables?

Post by Ambush Commander »

I admit, maybe this should have been in Databases, but it's conceptual and stuff, so I guess it's okay.

For the first time, I'm using MySQL. :D It's quite exhilarating finding out all the ways SQL is so powerful and so much more useful than plain old flat text files (ugh, I'm never going back to them again for relational storage).

[rant]
It's also kinda frustrating too though. I tried to figure out how to use the CREATE TABLE syntax from the MySQL documentation but I gave up and cheated and used PHPMyAdmin. I create a table that looks about right, and then as I start loading data I tweak it a bit, until I get the design I want. It's pretty annoying sometimes, because for one of my tables I had to splice out one of the values I took for granted (it was "category") because there could be multiple assignments for just one entry. Then, as I was designing the Category Tree table (that maps the relationship between categories) I found out that I had to, once again, split the table into category hierarchy (the hierarchy would be redesigned to do preordered tree traversal which I barely understand) and subcategories (don't ask). So now I have three tables on categories and I'm going to have to make querys for all of them and learn new types of algorithms and it's all gotten so complicated. So much for the tweak and run method.
[/rant]

When you have to accomodate for some novel sort of datatype inside a relational database, how do you go about designing the table?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

First, read some web tutorials about "normalization". If they talk about "denormalization for preformence" ignore that for now.

Then start to brainstorm about the "things" in your application and their "properties" (kinda like a OO class design)

Anywhere where the property has a 1:1 mapping to the object, add it to the table. Anywhere where the object contains several same-type properties (a list) make a subtable for it. Repeat, referring back the the rules of normalization.

Shorter answer (but longer to execute), buy Chris Date's Introduction to Database Systems and reserve about a month to work through it :)

Medium answer, post what you have so far and we'll give feedback.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

It's all about the planning.. Designing a database is an absolutely key stage of developing a website, and so few people spend enough time on it. You really do just have to sit there and brainstorm *everything* you can think of that you're going to need later.

This is why having a well written specification is critical to producing a good website.
crazytopu
Forum Contributor
Posts: 259
Joined: Fri Nov 07, 2003 12:43 pm
Location: London, UK
Contact:

Post by crazytopu »

Entity-Relationship Diagramming (ERD) is a technique you can use to design your database. Do a google and I am sure there are plenty of good tutorials out there. You done the work by trial and error basis but if you can build up an effective ERD you are almost there. Once there is an ERD the very next step is to run the SQL to build up the real table (what is called "relation" in relational database paradigm)

Depending on your situation you choose either "normalisation" or "ERD". But a rule of thumb that is used to determine which technique to go for you ask if you have all the dataset in place, if yes, you go for normalisation. If you know that you wont have the complete dataset in hand and you are going to collect them step by step, you go for ERD.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Before you start with ER modelling you might want to read up on Normal Forms... And make some exercises to get the hang of it ;)

I believe (E)ERD can be useful if you want to generate a database schema, but remember that ERD allows n - m relationships, you have to introduce another entity so you split the n - m to n - 1 and 1 - n.

There are quite some products out there that offer you a visual database designer too..
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

The normal forms seems interesting (I read a tutorial and the Wikipedia article on Database normalization) but I noticed a few things:
Ronald Fagin demonstrated that it is always possible to achieve 4NF (but not always desirable).
Does that mean that even though there are 5/6 levels of normalization, it's usually good enough to go with three?
You really do just have to sit there and brainstorm *everything* you can think of that you're going to need later.
Does that mean that making a lot of extra columns that you don't plan on implementing until later is better than adding more columns when you need them?
Depending on your situation you choose either "normalisation" or "ERD". But a rule of thumb that is used to determine which technique to go for you ask if you have all the dataset in place, if yes, you go for normalisation. If you know that you wont have the complete dataset in hand and you are going to collect them step by step, you go for ERD.
Aha, that was very useful. I already have lots of data to work with, so it's normalization for me!
There are quite some products out there that offer you a visual database designer too..
Hmm... they're probably paid. PHPMyAdmin for me.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Regarding the levels of normalization... I'm evolving more and more towards a "relational database purist". Therefore I push very strongly for 5NF for "regular" databases and 6NF or "temporal databases". However, I accept that some people feel that preformence can suffer (especially with some databases and untuned configurations) and would say that its borderline acceptable to stop at 3NF.

However I'ld probably still start with 5NF and only after some profiling move back to 3NF only if absolutely required.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Hey, Nielsene, if you have some extra time, could you hop over to the Wikipedia article on database normalization ( http://en.wikipedia.org/wiki/Database_normalization ) and take a look at the examples? Most of the resources I've found on the web explain 1NF - 3NF fairly well, and then stop. It would be nice if Wikipedia had some good examples of 5NF and 6NF (what's nice is they've already got a good example of 4NF).
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

The only reference I've seen for 6NF is the book by Date, Darwen, and Lorentzos Temporal Data and the Relational Model. Its a tough read and a tougher concept to apply as it requires a more correct implementation of relational database theory than SQL allows. I'm slowly developing enough custom functions, code generators, etc to mimic it in SQL, but its tough. However, if your going to deal with 6NF I think you basically have to read that book. No web tutorial is going to explain it in such a way as to be useful; the only one I've seen makes it sound like a useless nightmare....
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

A useful "Free" package to display a database design with MySQL is dbdesigner4

You need to know the basics first though.
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Cool... I'll check it out.
Post Reply