Database design - referentail integrity (1 Viewer)

[Fransdekock]

Hi i have a quick question

Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.

My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?

Anyone have any views on this ?

Ur help will be much appreciated

Kind regards
Frans

 

[Bodisathva]

Generally, you would want a "live" invoice to be updated by the data contained in the pricing tables, but a historical invoice should not be.

I suppose, in answer to your question, if you force the foreign key/data aspect to all invoices, your historical data would be changed as well. I would only link that data at invoice creation.

 

[Fransdekock]

How do u link it just at invoice creation ? Excuse me asking but this is new to me

 

[Bodisathva]

I suppose "link" is a bad term...

When you are creating an invoice, you would want to select the part number and retrieve the unit cost.

Since you need to retain historical data, you should store part number and unit cost in your invoice table as opposed to a part number linked to a parts table where it is the pKey to a unit cost field. Otherwise, as costs change, historical costs and invoice totals change, causing the data to be invalid.

You could, possibly, link the two tables to insure that the only part numbers that go on an invoice are contained in the inventory table, but that will create problems for new and discontinued parts.

I can honestly only think of one time that you may want to "join" the two tables in a query, and that would be for historical pricing analysis.

 

[Fransdekock]

Thx for ur help

I have an idea dont know how well its going to work. So using ur idea of keeping the unit price on the invoice table what if i keep it in both tables. Invoice and products. So at creation of the invoice or when a new invoice id is created it takes the current price and inserts it into the table. This is done only at creation time using sql insert into command. Therefore keeping the price right ?

U think this will work ?

 

[Fransdekock]

Thx for ur help

I have an idea dont know how well its going to work. So using ur idea of keeping the unit price on the invoice table what if i keep it in both tables. Invoice and products. So at creation of the invoice or when a new invoice id is created it takes the current price and inserts it into the table. This is done only at creation time using sql insert into command. Therefore keeping the price right ?

U think this will work ?

 

[Fransdekock]

Thx for ur help

I have an idea dont know how well its going to work. So using ur idea of keeping the unit price on the invoice table what if i keep it in both tables. Invoice and products. So at creation of the invoice or when a new invoice id is created it takes the current price and inserts it into the table. This is done only at creation time using sql insert into command. Therefore keeping the price right ?

U think this will work ?

 

[Bodisathva]

Right! Only the current price should be maintained in the inventory table, the prices in the invoice table will be for historical accuracy.

That's the ticket, laddie

 

[Fransdekock]

Thx so much for ur help.....

appreciate it!