Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    handling size, color, quantity in db design

    I'm setting up a database for a school project, and I'm wondering how to handle sizes, colors, and quantity (OnHand).

    Currently, in my Products table, I have:

    ProductId, ProductName, Category, Description, Cost, Price, OnHand, and Supplier, as field. I'm a little confused though, because, one may have 22 of product x as one row, but that doesn't tell you what sizes and colours are amongst the 22 products of that type.

    I guess a bad way would be to have a record for each product x of a certain colour and size, but that seems pretty bad.

    How does one handle this?

    Thanks.

    Tomasz

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: handling size, color, quantity in db design

    Either attributes such as colour and size should be part of the Product, with different product IDs for each, or you need a separate table e.g. Product_Variant that has this information linked to the Product.

  3. #3
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    The least of your worries is the number of rows you generate.

    Yes, break them all down by size, color and quantity on hand.

    Color(ColorID, ColorName)

    Size(SizeID, SizeMeasure)

    Product(ProductID, ..., OnHand, ColorID, SizeID)

  4. #4
    Join Date
    Jan 2004
    Posts
    9

    Re: handling size, color, quantity in db design

    Originally posted by andrewst
    Either attributes such as colour and size should be part of the Product, with different product IDs for each, or you need a separate table e.g. Product_Variant that has this information linked to the Product.
    Thanks very much.

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    Originally posted by certus
    The least of your worries is the number of rows you generate.

    Yes, break them all down by size, color and quantity on hand.

    Color(ColorID, ColorName)

    Size(SizeID, SizeMeasure)

    Product(ProductID, ..., OnHand, ColorID, SizeID)
    Thanks. That is quite clear, but I'm wondering about one thing. If there is a ProductID 12345 that has different size, color variants, then the ProductID is no longer unique (currently I'm using it as primary key). What I mean is: there may be a ProductID 12345 with 10 in medium, red, and ProductID 12345 with 5 in small, black.

    I guess, then, I should be using ProductNumber where I'm currently using ProductId, and then have ProductId unique?

    Is this right? Excuse my ignorance. I'm learning...

    Tomasz.

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    You would create a unique constraint on product number, size and color and use Product ID as a surrogate key.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    certus, perhaps you meant to say use another field as a surrogate key

    like, an autonumber

    you can't "use ProductID as a surrogate key" because chances are, it has values like 'XHV203' and '0045YY'

    you just automatically assumed ProductID was a meaningless number, didn't you



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

  8. #8
    Join Date
    Jan 2004
    Posts
    9
    Originally posted by r937
    certus, perhaps you meant to say use another field as a surrogate key

    like, an autonumber

    you can't "use ProductID as a surrogate key" because chances are, it has values like 'XHV203' and '0045YY'

    you just automatically assumed ProductID was a meaningless number, didn't you



    Okay. so I use ProductNumber (autonumber) for key, and then have as many records as there are variants of ProductID 12345?

    So, if my Products table schema was:
    ProductNumber, ProductID, ProductName, Color, Size, Cost, Price, OnHand, then my records would look like this:

    1, 12345, My Widget, Red, S, 99.00 , 125.00, 33
    2, 12345, My Widget, Red, M, 99.00 , 125.00, 22
    3, 12345, My Widget, Blue, S, 99.00 , 125.00, 10
    4, 12345, My Widget, Blue, M, 99.00 , 125.00, 19

    Is that the way? By the way, what exactly is a "surrogate key". Is it simply a primary key that is not from the business data, but created to provide a primary key?

    Thank you all for helping this newbie.

    Tomasz

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tomasz, that is right, your design is good, and yes, a surrogate key is "not from business data" -- a key from business data, like ProductID, is called a natural key

    it is not necessary that the surrogate key be the primary key, but that is by far the most common application
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Posts
    9
    Originally posted by r937
    tomasz, that is right, your design is good, and yes, a surrogate key is "not from business data" -- a key from business data, like ProductID, is called a natural key

    it is not necessary that the surrogate key be the primary key, but that is by far the most common application
    Thanks Rudy. I will set up this way. Just wondering, though, back a bit in this thread certus said:

    The least of your worries is the number of rows you generate.

    Yes, break them all down by size, color and quantity on hand.

    Color(ColorID, ColorName)

    Size(SizeID, SizeMeasure)

    Product(ProductID, ..., OnHand, ColorID, SizeID)

    What would be the benefit of seperate tables for Color and Size, with
    foreign keys to those tables in the Products table? Just wondering.

    Thanks again.

    Tomasz.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the benefit of those separate color and size tables is that they hold the definitions that the main product table "links to" with colorID and sizeID

    this means you don't put the color name and size measures into the product table, and this in turn means that if you wanted to change the name of "fuschia" to "flaming purple" for example, you can do it in one place, and all products which have that color will, when they are printed out, show that new color name

    tables like that, consisting of an ID and a description, are very often called "lookup" tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2004
    Posts
    9
    Originally posted by r937
    the benefit of those separate color and size tables is that they hold the definitions that the main product table "links to" with colorID and sizeID

    this means you don't put the color name and size measures into the product table, and this in turn means that if you wanted to change the name of "fuschia" to "flaming purple" for example, you can do it in one place, and all products which have that color will, when they are printed out, show that new color name

    tables like that, consisting of an ID and a description, are very often called "lookup" tables
    So, lookup tables are not a necessity but a convenience? Do you think lookup tables are a better idea, rather than putting color name and size name in the product table? What is your opinion? Do it, don't do it? This is an exercise I'm doing for Coldfusion programming class, so I'm trying to model real world 'best practices'. I guess it might depend on the number of products a business has, how many variants, etc.

    Thanks again.

    Tomasz.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    definitely, do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2004
    Posts
    9

    many thanks helpers

    Originally posted by r937
    definitely, do it
    Thanks, I did. Works like a charm. Many thanks Rudy and all.

    Tomasz.

Posting Permissions

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