Table Design- relationships | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table Design- relationships

Heya,
I have a question on database design…Two of the tables in our DB are Customers and CustomerEnquiry. Customer holds details on Customers (Uniquely identified by TransactionId and CustomerType- can have many customers for a TransactionId). CustomerEnquiry holds details on enquiries made on a Customer(Uniquely identified by TransactionId, CustomerType and EnquiryDate- can have many enquiries on a customer). The relationship between the two is on TransactionID and CustomerType. We do a lot in our system based on the last enquiry on a customer, and are adding going to add a column to the Customer table to store the date of the last Enquiry (at the moment there is a view to find the last enquiry- we are aiming to get rid of the view). We will add a trigger to the CustomerEnquiry table to update the date in Customer when there is a new enquiry. So, my question is: do i create a realationship between the EnquiryDate in both tables? If so, do i add it the the existing relationship, or add a new one?? Thanks
Ben
Hi Ben you can’t express that relationship in sql foreign keys, so you’d need to enforce it in triggers… the relationship that you are trying to make ought to see transactionid, customertype and enquirydate all put into the customers table (as well as its own pk). You can’t express it if you’re only adding enquirydate to customers. The only way you could do this using sql foreign keys is to create a surrogate primary key (i.e. identity field) on customerenquiry and then add that surrogate pk as a foreign key in customers Cheers
Twan
]]>