I have items (tracked with itemid
) that have attributes assigned to them (field1
,field1
for example). At any given time, field1=val1
, field2=val1
, ..., fieldn=valn
where val1
,val1
,...,valn
= 1..100. and n
= 1..20
I need to design a DB to track the changes for each field, and I'm not sure if I should list each field as a column (for example table1
), or list each change as a row (for example table2
).
create table1 (datetimeofchange datetime2, itemid bigint, field1 smallint, field2 smallint, field3 smallint, field4 smallint, ..., fieldn smallint)
create table2 (datetimeofchange datetime2, itemid bigint, fieldval smallint, changeval smallint)
create table2lookup (fieldval smallint, fieldname varchar(50))
What are the trade-offs I get from choosing between the two designs? Is there another design which would have different trade-offs? Is there some terms I can google to research this issue?
Thanks.