What's YOUR DB design?

[goober]

Hey guys,

What are the best practices you use to normalize all of your data to the fullest extent possible. If you don't normalize at all, I know it will catch you and bite you on the backside.

This thread is intended to be I guess a point of reference for the community as far as database design goes. I'd appreciate everyone's imput.

Also, if you have a database design that you would like looked over, post it in text format here.

[siphilp]

Now that is spooky both of us submitting a thread of the near enough the same nature...around the same time.

LOL


Si

[JimBolla]

The 5 rules of DB normalization:

1. Eliminate repeating groups.
2. Eliminate redundant data.
3. Eliminate columns not dependant on key
4. Isolate independant multiple relationships
5. Isolate symantically related multiple relationships


I typically follow these more or less. Sometimes there are cirmumstances where it's easier/faster/more efficient to denormalize. Obviously, theory of database design (what tables and what fields you should have where) is a pretty big topic to explain in a little text box. There's probably some decent reading material on the web that explains it. Maybe someone else can post some useful links.


Here's some style and symmantically-oriented guidelines I follow:

1. Name tables fields as accurately and descriptive as possible without making it too verbose. Only abbreviate when its very obvious, e.g. "Qty" for "quantity". Also, I prefer to name my tables in the singular form, e.g. "Employee". I used to name them in the plural form, e.g. "Employees" but when you start coding a large data entry system and start reusing code and cutting/pasting alot, the singular form makes it go a little bit smoother, IMO.

2. Every table has a primary key field named the same as the table plus "ID", e.g. "Employee" table has a primary key field named "EmployeeID". Foreign key fields are typically named the same as the field they point to, although exceptions to this are relatively frequent, e.g. "Employee" table has a "BossID" field that has a foreing key to the "Employee" table's "EmployeeID" primary key field.

3. If you have a multiple-to-multiple relationship between two entities, it requires three tables, one for each entitiy, and one to represent that relationship. For example:

You have a table called "Employees" and a table called "Certifications." Each employee can have more than one certifications, and each certifications can be had by more than one employee. This scenario would require 3 tables:

.....Employee
..........PK: EmployeeID

.....Certification
..........PK: CertificationID

.....EmployeeCertification
..........PK: EmployeeCertificationID
..........FK: EmployeeID
..........FK: CertificationID

4. Whatever your style, be consistent. This probably seems obvious but its amazing how many people don't do it.