table design questions
Date: 15 Mar 2002 14:21:02 -0800
Message-ID: <6b50d18.0203151421.60ab0775_at_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 - 23:21:02 CET