SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict AdRock952's Avatar
    Join Date
    Aug 2006
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Some advice on database theory

    I have a database full of products which obviously have their own product ids

    There comes a time now when some of my products are the same thing but in different sizes (i.e. T-Shirt S, M, L and XL). They would all have the same name but with a different product id, but how would i group them for output.

    On the site where it's going to appear, i would like a select box with the different sizes for the product (if there are different sizes).

    I would imagine each product would have it's own product id and in another field there would be a sub id. This could be left blank if there are no other sizes.

    How would anyone else do this?

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    The correct way of doing this is to create another table where you have rows that represent a size of a product.

    Something like

    Code:
    product
    -----------
    id
    name
    color
    (etc)
    
    productsize
    ----------------
    productId
    size
    And then just add the sizes for a product to the productsize table one by one. When you show a product just JOIN it with the productsizes (or do a separate query, up to you) and show the dropdown if there are multiple sizes.

    An alternative would be to store all sizes in a comma separated field in your products table, so you get a field with a value like "S,M,L,XL". You can split the string on commas and get an array of sizes.
    The drawback of this method is that becomes harder (or even impossible) to search in the field; i.e. if someone wants to see all blue medium t-shirts that would be harder.
    It's also not possible to keep extra information with the sizes (like stock, "we have 4 medium, 3 large, etc on stock").
    However, if such searches will not be needed and you don't need to add extra information, but you're just using it for lookup, this way (though incorrect) is heck of a lot easier.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    It's also not possible to keep extra information with the sizes (like stock, "we have 4 medium, 3 large, etc on stock").
    you haven't shown how that would be done in your one-to-many table, either

    besides qty_on_hand, something else that may vary by size is the price

    now, if you try the same approach, and have a separate one-to-many table for prices, with productid and price as the columns, you immediately run into a problem -- how to match the correct price to each size

    follow this line of reasoning far enough, and you will eventually discover another approach altogether, and that is not to have a single product row and then mangle the sizes and prices and other variable stuff in some way, but simply to have everything in the products table

    thus, if the t-shirt comes in S, M, L, and XL, then there will be 4 rows in the product table

    which is what adrock said... "They would all have the same name but with a different product id"

    often, the simplest solutions are the best

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    which makes me wonder if this might be the way forward?

    create table products
    ( id int not null auto_increment primary key
    , product_number varchar(24) NULL
    , product_name varchar (99) not null
    ) engine=innodb etc

    create table product_prices
    ( id int not null
    , product_id int not null
    , price decimal(6,2)
    , currency_code char(3)
    ) engine=innodb etc

    create table product_attributes
    ( id int not null
    , product_id int not null
    , attribute_name varchar(99) eg colour/size/weight
    , attribute_value varchar(24) not null eg red/44/250
    , attribute_unit_of_measurement varchar(6) NULL eg kg/quart/gall/ml
    ) engine=innodb etc


    Though I can't work out for you, how to make the structure work if the price may (in some cases), be specific to the size (for example) and in others be standard for all variations of attributes.

    hth somehow.

    search a suggestion oddz made a few weeks back, on which this suggestion is based.

    bazz

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    you haven't shown how that would be done in your one-to-many table, either
    I assumed that would be trivial, but okay. Here it is:

    Code:
    product
    -----------
    id
    name
    color
    (etc)
    
    productsizes
    ------------
    productId
    size
    stock
    Quote Originally Posted by r937 View Post
    now, if you try the same approach, and have a separate one-to-many table for prices, with productid and price as the
    columns, you immediately run into a problem -- how to match the correct price to each size
    Make (productId, size) the primary key of productsizes and then use that tuple to identify product and size ordered.

    Quote Originally Posted by r937 View Post
    follow this line of reasoning far enough, and you will eventually discover another approach altogether, and that is not to have a single product row and then mangle the sizes and prices and other variable stuff in some way, but simply to have everything in the products table

    thus, if the t-shirt comes in S, M, L, and XL, then there will be 4 rows in the product table

    which is what adrock said... "They would all have the same name but with a different product id"
    Which is prone to update anomalies, my suggestion is not.

    @IBazz what you're suggestion is known as Entity-Attribute-Value (EAV) and should IMHO be avoided like the plague -- it's only to be used if all else fails.
    I don't think all else has failed in this thread just yet
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    I assumed that would be trivial, but okay.
    but it isn't trivial at all, as i shall now demonstrate

    Quote Originally Posted by ScallioXTX View Post
    Make (productId, size) the primary key of productsizes and then use that tuple to identify product and size ordered.
    nice try, but what if the product doesn't come in sizes?

    since size is part of your PK, it can't be null, so you're going to suggest creating a dummy size, aren't you

    eeeewwwwwwww

    Quote Originally Posted by ScallioXTX View Post
    Which is prone to update anomalies, my suggestion is not.
    wha?? please show an example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    since size is part of your PK, it can't be null, so you're going to suggest creating a dummy size, aren't you
    Not necessarily. You could also set a price in the product itself that is used when there are no sizes.

    Quote Originally Posted by r937 View Post
    wha?? please show an example
    What I gather from your posts is that you're proposing something like this

    Code:
    id  name         size    description
    ---------------------------------------------------------------
    1   T-shirt 1    S       This is a ncie t-shirt that .... (etc)
    2   T-shirt 1    M       This is a ncie t-shirt that .... (etc)
    3   T-shirt 1    L       This is a ncie t-shirt that .... (etc)
    4   T-shirt 1    XL      This is a ncie t-shirt that .... (etc)
    And than you would sort of "group by" (not in the SQL sense) name so that T-shirt 1 only shows once on the product listing, right?

    As you can see I've made a typo in there (completely by accident ): "ncie" should be "nice". Using this scheme I should update all 4 rows myself? Or I should let the application handle it, which is also not very nice IMO (it could crash half way ...). Plus I'd have to store the description multiple times (redundancy).

    (Okay, update anomaly was not the correct term here. Sorry for that one.)
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Plus I'd have to store the description multiple times (redundancy).
    have you ever seen any of my posts with regard to firstname?

    suppose you have a customer or employee or whatever persons table, in which you store the firstname and lastname of people

    a lot of people are named John, but does this mean you have to split off the first name into its own "firstnames" table, to avoid the redundancy?

    three answers:

    1. no, that'd be silly

    2. redundancy isn't always bad

    3. even if you replace the firstname with a lookup table, you haven't eliminated the redundancy, because the same number of (redundant) FKs will be pointing to John in the firstnames table

    so redundancy isn't an issue

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll start a new thread for my question Scallio. I may have confused the reader with my eg values for the table.

    bazz

  10. #10
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,032
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    have you ever seen any of my posts with regard to firstname?
    I have, but that's a totally different issue IMO.

    The Johns actually all have different names (even though they are the same) in that if one John decides he doesn't want to be called John anymore and changes his name to Bob, I should only change that John to Bob, not all Johns.
    In the case of the product description, if I want to change that, I need to change it in all rows that pertain that specific product (unless you'd want a completely different product description for each size, which seems unlikely).

    Unless I'm missing something here?
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •