SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's YOUR DB design?

    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.
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  2. #2
    SitePoint Guru siphilp's Avatar
    Join Date
    Nov 2001
    Location
    Fife, Scotland
    Posts
    663
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now that is spooky both of us submitting a thread of the near enough the same nature...around the same time.

    LOL


    Si
    Last edited by siphilp; Feb 1, 2002 at 02:28.

  3. #3
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •