Basic question on relationships

[richlewt]

Hi
Please excuse me for this basic question but here goes. I am trying to write a simple database to handle a stocklist and orders. One table contains all the items and one table contains the orders. I have created all the links and everything is fine except for when I change the price of one of the items in my items table all records of that items that have been sold in the past now updates to the new price, I need the old records to stay at the old price. I know there is a simple answer so anyone please.

 

[doss]

Maybe on your link you have it set to Cascade Update?

That's the only think I can think of without seeing it.....

 

[jeremie_ingram]

What type of setup do you have for the products fields in your order table? Are they Look UP?

 

[pono1]

Rich,

Two basic comments on relationships:

1. Consider staying single if it's not too late. Or at least marry into money.

2. Create a separate field for price/paid/amount in your items/transactions table and store the info with each transation record since, as you have noted, prices change over time.

Regards,
Tim

 

[richlewt]

Hi
Thanks for your reply, I could indeed store the prices in the invoices/order table but I really wanted it to look for the latest prices in the items table and apply that price whatever it is in that particular point in time to that item, otherwise I have to keep manually typing in the price which could lead to errors. Or am I asking for something that is too complicated to achieve?
My thinking was that in the items table all relevant data against each item i.e. size, colour, description, PRICE would be stored there. when one was sold it would lookup that item, extract all the attributes and write it against the customer, but obviously the price will change.
Many thanks for your kind responses.

 

[pono1]

Rich,

Your idea makes sense: but I think you would want two tables. A price table and a products table.

Roughly, the price table will have fields like these so you can use it to look up a price at a certain point in time.

idNum (primary key)
productID (foreign key matching primary key in product table)
price
StartDate
EndDate

You can then query the table for a price based on the date of the transaction and the productID.

Regards,
Tim

 

[richlewt]

Thanks Tim, now that makes sense, I will give that one a little go. Thanks again for all your help.

 

[pono1]

You're welcome -- but you mean to say staying single or marrying into money didn't make sense? Ah, another romantic.

Regards,
Tim

 

[neileg]

Inventory applications are always a problem for new developers. The single table approach simply doesn't work. Some of the problems include:
1) Changing prices (as you have found)
2) Embedded stock balances are unmanageable
3) Lack of audit trail
and lots of others...

As a first step, look at some of the posting in here about stock, inventory, etc.