Dear all,

I am designing a new database to manage orders and articles.
I have one table named "Articles", with the following fields:
ART_Code,
ART_Name,
ART_Supplier,
...,
ART_Price,
ART_Active,

It is supposed that some article prices increases every year, and, of course we need to keep old data, so Wich one of the following solutions you recomend?

a) Keep the current desing, so when a new price is provided we "deactivate" the article and create another one with the same data and different price

b) Remove the price from this table and Create a new one with the field Price on in
e.g "Article prices" fields
ARP_Code
ARP_Price


Thanks & Regards.

solution b) lets you track only price changes, so solution a) seems more flexible

also, for solution b), you'll need an effective_from column

of course, you won't need an effective_to column, because that would require some shenanigans, possibly involving the dreaded NULL, so your best bet is simply to avoid that nonsense altogether, and just record the effective_from dates, and then the effective_to information can be gleaned from the fact that it's always the latest effective_from row that is current

easy peasy

With solution a, what does deactivating an article entail? What is the primary key of the article table? Will deactivation result in a violation of the primary key?

Ravi

Dear R937,
Regarding B), yes, you are righ we should insert a ART_Date_from field.

Doing this change, would option B) be more suittable for a data base design?



Dear rajiravi,

Regariding you questions, by deactivating an article I meant change value of ART_Active from True to False.

Primary key would be ART_Code.
If we change price, another code would be created.

Eg

Code Name Supplier Price Active
----------------------------------
0001AA Art1 001 15.3 True
0002AA Art2 002 80.3 True

If I change price of Art1 to 15.9 then we will have

Code Name Supplier Price Active
----------------------------------
0001AA Art1 001 15.3 False
0002AA Art2 002 80.3 True
0001AB Art1 001 15.9 True

Orders made before price change, would not be affected.
Orders made after, would use the new code.

Thanks & Regards.

Yes. Queries might be simpler if an effective_to date were also included. But that is your choice.

So what is the purpose of having th art_code column? It seems to act like a sequence generated ID column. The "name" column seems to be more meaningful.

Ravi