SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard subnet_rx's Avatar
    Join Date
    Aug 2001
    Location
    Hattiesburg, MS
    Posts
    1,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Store DB Design question

    Let's say I have blue widgets and red widgets in two different tables. Then I have a Sold table. How should I set the Sold table up?

    One column that could contain the id of the widget?

    Or a seperate categoryID that links to a table that describes the different widget categories?

    Or .....?

  2. #2
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First question is, why would you have your products in two different tables? I would set up a unique identifier (maybe a sku) as the primary ID in your product table. In your sales table, you can use an orderId as the Primary key along with the customerID to tie the sale, user and product together. You might also want to consider a lookup table to keep your sales table in check. This way you can keep your orderId as your Primary key and query your sales correctly. If you have multiple items in your order, you can use the salesItem table to iterate through the items in your order. There would be no primary key defined in this table.

    You should read up on database normalization before you go any further.

    tableProducts ---
    sku (or productId) (PK)
    name
    description
    price
    ...

    tableCustomer ---
    customerId (PK)
    name
    address
    city
    ...

    tableSales ---
    orderId (PK)
    CustomerId
    dateEntered
    ...


    tableSalesItem---
    orderId
    sku

  3. #3
    SitePoint Wizard subnet_rx's Avatar
    Join Date
    Aug 2001
    Location
    Hattiesburg, MS
    Posts
    1,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the reason I have them in two different tables, is because they have different properties that I am saving.

    Let's say I have a cars and trucks, would you put them in the same table? They have some of the same base properties, but other than that, they are very different. I mean, I am asking, would you just mark those items that cars don't have as NULL, or put cars in a seperate table?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is the classic supertype/subtype question

    yes, you would put the common parts into the same table

    then, whether you have separate tables for the columns that are unique to one type or another, that all depends on a number of factors

    but in general, it is usually a lot easier to have only one table, with NULL in those columns that do not apply to a particular row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard subnet_rx's Avatar
    Join Date
    Aug 2001
    Location
    Hattiesburg, MS
    Posts
    1,085
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply. I'll do some reading and work on combining the tables.


Bookmarks

Posting Permissions

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