table design questions

From: YHC <Chang_Y_at_bls.gov>
Date: 15 Mar 2002 14:21:02 -0800
Message-ID: <6b50d18.0203151421.60ab0775@posting.google.com>

Hello,

If you are interested in the following table design questions -

A table originally contains the following columns:

Item_Code
Effective_Date
A
B
C
D
E

Key: Item_Code, Effective_Date.

And, whenever A, B, C, D, or E is changed (can be one or more fields), the Effective_Date will reflect that.
The question is - If users want to add Effective Date for D, E changes other than A, B, C changes.
Is it better to add an extra effective date to the table (ie. DE_Effective_Date and rename Effective_Date to ABC_Effective_Date)? Or add an extra column to the table to indicate this is for ABC change or DE change or both? Or split the table into two tables - one table with Item_Code, Effective_Date, A, B, C; and the other table has Item_Code, Effective_Date, D, E?

Also, if there is an audit trail table for this. Assume the audit trail table contains:

Item_Code
Effective_Date

Update_Date_Time
Update_By
Update_Column

Old_Value
New_Value

If users want to update Effective_Date as well (so the Update_Column will be Effective_Date), is the audit trail table design OK? (Note - Item_Code & Effective_Date is the primary key of the original table.)

Thanks.

YHC Received on Fri Mar 15 2002 - 16:21:02 CST