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

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