Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Please Help About Master and Transaction Table Design

    hi,

    i have confusing problem about master and transaction table design,
    I have master table, "Item" and have 2 transaction table "Purchase Request Detail" and "Purchase Order Detail".

    i have a friend that design the database like this :

    Item
    =====
    itemid
    itemname
    stock
    price

    Purchase Request Detail
    ==================
    itemid
    itemname
    qty

    Purchase Order Detail
    ================
    itemid
    itemname
    qty
    price

    the item name in P.R. Detail is get from Item,
    but item name in P.O. Detail is get from P.R
    My friend say that the item name in transaction is used for tracing if some one already changed the name in master item.

    But what i think it's wrong because why is item name field included in transaction table? ,

    so which one is true? and can i have an explanation..?
    thank you so much..

  2. #2
    Join Date
    May 2008
    Posts
    277
    My friend say that the item name in transaction is used for tracing if some one already changed the name in master item.
    So when you talk about "renaming" an item, there are two cases to consider:

    1) An actual renaming or "rebranding" of an item. You need to determine how you want to handle this, but personally I'd simply enter a new item and mark the old one as being discontinued -- "The Hoobdobber's been replaced by the Thingbopper."

    2) Updating an item name because it was initially entered wrong -- "Oops, it's a Thingbobber, not a Thingbopper". In this case, you want the changes to be visible where ever the name may appear.

    In either case, I'd leave the item name out of the purchase request and purchase order tables. I think you're setting yourself up for a logistical nightmare if you copy the item name for each purchase. "This purchase order says we sold them a Hoobdobber, but it's listed as a Thingbobber. And this one says we sold a Thingbopper -- is that supposed to be a Thingbobber?"

Posting Permissions

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