Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Orders & Articles database design

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Quote Originally Posted by Serruiki
    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.
    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

  4. #4
    Join Date
    Jun 2007
    Posts
    2
    Dear R937,
    Quote Originally Posted by r937
    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
    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.

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Quote Originally Posted by Serruiki


    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?
    Yes. Queries might be simpler if an effective_to date were also included. But that is your choice.


    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.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •