Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    22

    Unanswered: Linking Product Unit Price to Order Details Table

    Hi,

    Making a database for a clothing business.

    I have an Order Details table (Order Line) which holds information on a clients order, including products, quantities, etc.

    I also have a products table which lists all of the products we stock, including code number, colour, size, product type etc. and prices.

    Instead of having unit prices in both tables, is there a way that when i enter in a product code into a client order (order line table) some of the information, like unit price automatically fills in?

    Thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    You're right in thinking that you have redundant data. Link the tables together on the Product ID, and in the name of normalization, I would delete the cost out of the Orders table.
    Me.Geek = True

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    You're right in thinking that you have redundant data. Link the tables together on the Product ID, and in the name of normalization, I would delete the cost out of the Orders table.
    Me.Geek = True

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Sorry about double post
    Me.Geek = True

  5. #5
    Join Date
    Jun 2007
    Posts
    22
    but in the northwind traders example database and another more recent template for an order management database, they have unit price in both the order details table and the products table

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    which is fair enough
    after all the price on a specific order is the price applicable to that order. when you look back over time you want to know what price was in force at that time for that order... in my view it therefore makes sense to have that price associated with that item in that order

    whereas your price in your products table probably reflects the current price that you would charge on new orders.

    its arguable that if you only have one price for any one product line (ie there are no case discounts, volume discounts, special offers etc then you could archive the price (ie store every price change. however in my view potentailly that breaks the link between the order and the price the customer has been quoted or expects to pay.

    you could store things like discount or volume rules separately, bytut then you would need to associate what discount rules have been applied to that order ot that order item.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2007
    Posts
    22
    The thing is, we aim to sort the database by season.

    Each season we get a new stock list to fill in which we decide. Therefore, as these products and proudct codes only last for a few months, there will be no price changes or different prices for different clients. Apart from maybe a simple discount field which can be added on the end of the order details table as an attribute.

    You seem to have alot of knowledge. Ive just posted on another topic. Could you possibly have a look. Ive decided to try and get help from the start rather than try and sort out problems when i havnt got the basics right.

    thanks

Posting Permissions

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