March 14, 2007 at 11:22 am
I am designing a quotation application. Currently there is a Customer, Quotation and Quotation details table. PK – FK relations between Customers and Quotations, Quotations and Quotation Details exists. If the design conform to the 3rd normal form
No column should be duplicated. Now if the quotation is approved its data should not change again so its said to be historic data. Say for instance the customer that was used on the approved quotation name change in the Customer table then there will be no data consistency. The customer name on the approved quote will now be different – which is not acceptable. Will it be acceptable to denormalize the order table so that it has duplicate columns for the customer data? What suggestions do you have regarding this scenario?
March 15, 2007 at 7:17 am
Rudi,
I would break the Customer table into two tables: A CustomerMaster that includes your CustomerKey field and any other attributes that either never change or you don't need to record historical information for and a CustomerAddress (or History or whatever you want to call it) that contains the changing information. Store one row on the Address table every time the customer's address (or other information) changes with a date field that indicates when the row became effective (for reporting) and a PK field (for uniqueness and joins). When you add a Quotation, you need to make sure to store the key of the current Address row on the Quotation table. Then, when you have to query for a specific quotation, you join the Quotation table to the CustomerAddress table based on the AddressKey and the Address table to the CustomerMaster table based on the CustomerKey.
The structure will look something like this:
CustomerMaster
|
/|
CustomerAddress
|
/|
Quotation
|
/|
QuotationDetail
That's how I'd handle it. Someone else may have another solution for you.
Good luck,
Chris
March 15, 2007 at 8:38 am
Do you need to record changes, i.e. store old and new customer address?
Think about what type of reports stakeholders will require, this can aid in the design of the system. Don't leave reporting analysis to a time when you're far into the development cycle! Think about what future reports will the stakeholders may require. Good luck!
D
March 15, 2007 at 12:04 pm
Grasshopper,
The customer details(name or address...) on the quotation after it is complete should never change. So the problem is if the customer details change after the quote is comple there is no data consistancy on the completed quote. I hope it makes the situation more clear. Have any one of you came across this type of situation?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply