Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Design Question - Single general table versus individual specific tables

    Hi

    I'm currently designing a database schema to hold status updates (regarding orders). Each status update contains common fields and then some variation (say 2-3 fields) based on the type of status update. There are approx. 10 different status update types.

    As I see it, there's 2 ways to design the database to hold this info.

    1) Multiple Specific Tables

    a) A generic table for holding the common fields, and
    b) One table per 'Status Update' type, to hold the status update specific data in each case.


    2) Single General Table

    A single status table with enough columns to hold all of the status update variations (such that only the relevant columns will be populated on each row (in addition to the core fields).


    As I see it, option 1 is possibly more technically correct, as it omits the redundant fields associated with the second option and is more scalable. The second option however provides for a slightly simpler (flatter) database design and thus possibly better performance.

    I'd appreciate some thoughts on the above. Can both options be justified and thus what criteria should be applied in determining the best solution..

    Thanks in advance
    jg

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't see that option 1 omits "redundant" fields, nor that it's more "scalable"

    rather, it simply avoids NULLs, and is therefore in 5th or 6th normal form (i forget which)

    but "re-assembling" the data from umpteen tables with LEFT OUTER JOINs is a bitch

    usually i go with the single table and lotsa NULL values in any given row

    i use separate tables only if i'm forced to

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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