Suitable Table Design ?

Performance is something that you need to test out in your own
evironment and with your actual data. A DATETIME uses 8 bytes rather
than 4 bytes for an INTEGER but I don't see why it should impact
performance significantly in your case. You might alternatively
consider using SMALLDATETIME, which is the same size as an INTEGER.

As for AmendId being a "sequence number", just what value is that
information? A DATETIME defines the sequence accurately and the
date/time of the change is much more useful data to keep. If the
ordinal sequence *number* is important to you then you can derive that
in a query or just put a row count next to the detail history when you
display it client-side.

--
David Portas
SQL Server MVP
--

Hello David,

Would (contract_num,details_changed_date) key be efficient in Queries or
(contract_num,AmendId) key be efficient
in Queries - Note that AmendId is a sequence number for each of the
amendments.

Gopi


"David Portas" wrote in message

 > The problem is that both designs are denormalized. You have contracts and
 > their details in the same table. Once you fix this a DATETIME should be
 > adequate to sequence the history of changes:
 >
 > CREATE TABLE contracts (contract_num INTEGER NOT NULL PRIMARY KEY, custid
 > INTEGER NOT NULL /* REFERENCES Customers (custid)? I'm assuming the custid
 > won't change */)
 >
 > CREATE TABLE contract_details (contract_num INTEGER NOT NULL REFERENCES
 > contracts (contract_num), details_changed_date DATETIME NOT NULL DEFAULT
 > CURRENT_TIMESTAMP, concrtdate DATETIME NOT NULL, brokerid INTEGER NOT NULL
 > /* REFERENCES Brokers (brokerid)? */, context VARCHAR(8000) NOT NULL,
 > conexpdate DATETIME NOT NULL, PRIMARY KEY
 > (contract_num,details_changed_date))
 >
 > --
 > David Portas
 > SQL Server MVP
 > --
 >
 >