Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Design Dilemma - Rows or Columns ??

    Here is my design dilemma:

    I have a table that contains products for each supplier.

    | supplierid | prodid | price_each | price_case |

    This means that for every new supplier I add, I have to add records for all the products available to this table. I intend to have around 10 suppliers and probably around 500 products, so around 5000 records for option 1.

    The other option is to have this table structure:

    | productid | Supplier1_price_each | Supplier1_price_case | Supplier2_price_each | Supplier2_price_case | Supplier........ and so on till nth supplier.

    This means 500 products but 2 columns for each supplier.

    Which is better for both performance and design?

    The first option has more overheads that have to be covered in my php code to produce the extra rows to accommadate the new supplier as it is added.

    The second option only requires me to add 2 columns every time a new supplier is added.

    Not sure which otion is best.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by heals1ic
    Which is better for both performance and design?
    the first, hands down

    example 1: what happens in each design if you stop doing business with supplier 127?

    example 2: how do you count in each design how many suppliers supply product X?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Agreed with Rudy 100% (of course). You should get yourself a book on relational database design and look up "repeating groups".

    But I'm intrigued: how could adding 2 new columns to the table for a new supplier not have any impact on your php code?

Posting Permissions

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