Results 1 to 4 of 4

Thread: Database Design

  1. #1
    Join Date
    Apr 2007
    Posts
    9

    Unanswered: Database Design

    Hi,

    I'm looking to set up an ecommerce site for a client with around 1800 products in it. There will also be around 100 customers that receive different discounts on each of these products. unfortuanately this is not calculated as a percentage but rather each individual customer pays a different price for a pack of meat etc.

    I'm really just looking for some advice on how to design the database.

    I was thinking of making the following tables.

    A product table containing the product ID, title description etc.

    A price table with the product ID, customer Id, and then the price.

    And also a customer table containing the customer Id, company name etc.

    Is this a good way of doing it or can someone enlighten me as to a better way to do it.

    Cheers

  2. #2
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    14
    Hi.

    That sounds about right.
    If each product needs a different price for each customer, then a N:M relationship, somewhat like you described, is usually the best way.

    The only problem there is how you would store a "base price" for each product, if you need such a value.
    Personally, I would be tempted to add a row for each product in the price table, where the customer ID would be null, and use that to store a base price for customers that don't actually have a price listed.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Atli
    Personally, I would be tempted to add a row for each product in the price table, where the customer ID would be null, and use that to store a base price for customers that don't actually have a price listed.
    satan, get back, and take your evil temptations with you!!

    you couldn't do that anyway, since the PK of the table would be a composite key of { product ID, customer Id }, and no part of a PK can be NULL

    you do use PKs, right?

    the best solution is to add a column called base_price to the products table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Location
    Iceland
    Posts
    14
    Quote Originally Posted by r937
    satan, get back, and take your evil temptations with you!!
    O no, he's on to me!

    Quote Originally Posted by r937
    you couldn't do that anyway, since the PK of the table would be a composite key of { product ID, customer Id }, and no part of a PK can be NULL

    you do use PKs, right?

    the best solution is to add a column called base_price to the products table

    A good point. Not that this couldn't be done tho. You could use a auto_increment PK and set the two ID's up as a unique key.
    Or, if you are about to get fired and you hate the guy who is going to be replacing you... you could simply leave them to be duplicated.

    But all in all, you are probably right. Storing it in the product table would probably be best.

Posting Permissions

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