September 26, 2007 at 4:51 am
I have a few questions regarding RI in a simple Order database in sql server 2005. Firstly, say you have a basic Order database with tables- Customer, Order, Order Items. If an order is placed it is history data and should not change, so by changing any customer data related to the order is not allowed. What if the customer address change after the order was placed, you will end up with an order that shows a different address as to when the order was placed, which is inconsistent. If you create a history table of all current tables(Customers, Orders, Order Items) this will solve the problem. When the order is placed all the data of that order is moved from current tables to their history tables with a trigger. Is this the right way to go? Please send me your suggestions.
September 26, 2007 at 5:41 am
hi
do you have a separate address table or are the address details stored in the customer table ?
"Keep Trying"
September 26, 2007 at 6:28 am
It is the same table. Moving the address to a CustomerAddress table will solve this but what if the customer name changes? You will end up with a table for each column in the customer table.
September 26, 2007 at 7:14 am
I guess the question is - what's important within the context of the application? Is it important to be able to recreate past invoices with exactly the data that was on them? Is it important to make sure that the address of anything you reprint is current?
In general - I'd capture a change history on only those items that are deemed "important" to the specific app. So in a case like yours - I'd make sure I captured what specific price someone paid for each item, quantity, and perhaps a few other key metrics. Address on the other hand might not be an automatic, so I'd have to ask the "business app owner": after all - what good is an old, invalid address? How many do you intend to hold onto? That tends to add space rather quickly - if it's not an important metric, then don't set an audit on it.
As an alternative - if it's important to capture certain documents, like invoices - it might be worth looking at just that - capture a static version of the actual document (e.g. PDF version of invoice, etc..)
As to how I capture those - I usually keep my history captures in tables with very similar to the existing ones, although possibly "thinned down" to only those fields deemed important to capture in a history.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2007 at 8:25 am
Yes previous orders shoud be recreated with the data that was on the order when it was placed. So in the case of the address, if the address of the customer changed after an order was placed and a previous order is recreated it should not show the customers new address but the address that was on the order when it was placed.
September 26, 2007 at 8:57 am
Well then - it sounds to me that you want that recorded as part of the invoice itself. Meaning - if the ONLY place you care about having the historical record is within the order, then capture whatever you want to maintain for posterity in the main order record. So - in this case - everything that makes up the mailing address (customer name + address fields).
If this is the only use for it - no sense in complicating your "recreation process" with having logic figure out which previous version of the customer's address you need (think college student moving every semester and you needing to recreate an order from 2 years ago). You'd then just need to decide when is the appropriate time to copy the data from the main customer table is.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2007 at 11:17 am
If I understand correctly I must move the customer record that is used on the order to the Order Table (in our example the Customer name, Customer address ...).
September 26, 2007 at 12:23 pm
Meaning -
1. Keep the customer table as is for tracking CURRENT info.
2. At the point in time when an order is processed <snapshot> the relevant fields from CUSTOMER (sounds like cust.name + address fields) to matching fields in the ORDER table. I'd usually name them a little different to denote that they might not reflect current values.
that actually maintains the ability to use either address without needing rocket science to recreate the original invoice as is in the future (actually - you could use one single report/form/whatever to produce both current and old invoices).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 27, 2007 at 2:44 pm
I'll agree with Matt here. It comes down to what are your entities and attributes here. And it sound like for your needs here are that for your Invoice Entity that Customer addresses (etc) info at the time of the invoice are attributes. Your CURRENT table is the template, but at the creation of an invoice you would snapshot the info.
Of course this could also be handled via history tables for all with
ValidFrom and Validto type constructs. But that is SOOOO big of a pain to work with. Now this is a more flexible design, however both designs satisfy the requirements.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply