Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80

    database structure for many attributes


    Need some advice planning my database structure. I have about 1,000 products but its not a store. Currently my product table has 60 fields.I am about to add some more.

    Since some categories of products will not use all the entries is it ok to have many empty fields? Not every product will have a 'size' for example. Is there a more efficient way to store this data?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by mallen
    ... is it ok to have many empty fields?
    yes

    Originally Posted by mallen
    Is there a more efficient way to store this data?
    yes, it's called EAV -- entity-attribute-value

    it's very efficient for storing all kinds of different attributes, but please BE WARNED it's a ~bitch~ to query... i mean, you'd think a table with only 3 columns would be easy to query, but to extract any meaningful data means re-assembling the various pieces and you will go nuts trying to do it, and do it efficiently

    just google it to get a sense of how much people hate it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Thanks for the reply. I don't think I will need EAV or ready to take that approach. Bear with me just trying to plan ahead so I only do this once. Currently I have about 1,000 products(items) listed. Its not a store. So for example a shirt is item# 12345 and it comes is red, small and large.
    I don't list it with 3 unique IDs.
    I am planning to live filter my data like this site. https://store.hp.com/us/en/vwa/lapto...tandard-laptop If you are on the laptop section
    there are options for processor and screen size on the left side. But if you are in Accessories you will have options for size and color. Currently I have about 60 fields. How could I store an item for example that comes in red and yellow and no sizes?
    The next product would come in large and small and no colors? There will be alot of variations I will not use for all products. Would I have to post to all the fields (attributes) for every entry?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by mallen
    Would I have to post to all the fields (attributes) for every entry?
    no... google NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    You have just described exactly the scenarion that EAV was designed for. You have a shed load of different attrubutes but each product (product category) only has a small subset of those attributes. For each product you would only store values for those attributes that apply.

    Capture.PNG
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    This is the table structure I was planning. (sorry just can't get the formatting correct when I post)
    Code:
    products table
    ==============
    --------------+----------------+----------------+
    product_id | weight lbs  |   image        |
    --------------+---------------+-----------------+
    1               | 20              |   some.jpg    |
    2               | 25              |   another.jpg |
    3               | 16              |   globe.jpg    |
    --------------+---------------+-----------------+
    
    colors table
    =======================
    -----------+-----------------+
    color_id |  colors desc |
    -----------|------------------|
    1           |  Red             |
    2           |  Yellow         |
    3           |  Green         |
    ----------+-----------------+
    
    sizes table
    =======================
    -----------+--------------+
    size_id  |  size desc |
    -----------|---------------|
    1           |  Small       |
    2           |  Medium   |
    3           |  Large       |
    ----------+---------------+
    
    
    product attribute association table
    ===================================
    --------------------+---------------+-----------+-----------+
    prod_assoc_id | product_id | color_id | size_id  |
    --------------------+---------------+-----------+-----------|
    367                  | 1                 |    3        |    1        |
    368                  | 2                 |    1        |    3        |
    369                  | 2                 |    3        |    1        |
    --------------+------------+----------+---------------------+
    
    
    Result: 
    Product 1 only comes in Green and size Small
    Product 2 only comes in Red and Green and size Small and Large
    
    Quantity:
    There are two products that are Green
    There is one product that is Red
    Two products are size Small
    One product is Large
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    that product association tables breaks one of the normal forms, i forget which one, but you shouldn't have two attributes on the same row

    for example, you said "Product 2 only comes in Red and Green and size Small and Large" -- in which case you should have 4 rows for it, since that's the correct number of combinations, whereas your sample shows only two of them

    what you want is two separate tables -- one for product colours and one for product sizes -- and neither needs its own auto_increment id
    Code:
    product_colors table
    +------------+----------+
    | product_id | color_id | 
    +------------+----------+
    |     1      |    3     |
    |     2      |    1     |
    |     2      |    3     |
    +------------+----------+
    
    
    product_sizes table
    +------------+----------+
    | product_id | size_id  | 
    +------------+----------+
    |     1      |    1     |
    |     2      |    3     |
    |     2      |    1     |
    +------------+----------+
    by the way, this is one of those cases where i wouldn't bother with a surrogate key for colours and sizes --
    Code:
    colors table
    +------------+
    | color_desc |
    +------------+
    |  Red       |
    |  Yellow    |
    |  Green     |
    +------------+
    
    
    sizes table
    +-----------+
    | size_desc |
    +-----------+
    |  Small    |
    |  Medium   |
    |  Large    |
    +-----------+
    
    
    product_colors table
    +------------+------------+
    | product_id | color_desc | 
    +------------+------------+
    |     1      |  Green     |
    |     2      |  Red       |
    |     2      |  Green     |
    +------------+------------+
    
    
    product_sizes table
    +------------+------------+
    | product_id | size_desc  | 
    +------------+------------+
    |     1      |  Small     |
    |     2      |  Large     |
    |     2      |  Small     |
    +------------+------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    So the colors and sizes table woudn't have IDs?
    After working through it, since its not a store, I just need to display this item comes in this size or this color. Most of the 60 entries are just check marks and text.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by mallen
    So the colors and sizes table woudn't have IDs?
    no they wouldn't

    you don't have to follow my advice, but you also don't have to follow the advice of anyone who says every table has to have a numeric id, because that's just wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Originally Posted by r937
    you don't have to follow my advice, but you also don't have to follow the advice of anyone who says every table has to have a numeric id
    Based on the amount of posts you have, I think I will take your advice. I think this might work.

    Would I left join the product_colors and product_sizes tables? Now I have to work on the query.
    Last edited by mallen; May 30th, 2018 at 10:26 AM.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    inner joins, not left joins

    post your query if you have any problems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Code:
    SELECT DISTINCT product_colors.product_id, product_colors.color_desc, product_sizes.product_id, product_sizes.size_desc
    FROM product_colors
    INNER JOIN product_sizes
    ON product_colors.product_id = product_sizes.product_id
    The counts are correct just getting duplicates.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,794
    Rep Power
    4331
    Originally Posted by mallen
    ... just getting duplicates.
    that's unpossible, since you are using DISTINCT

    could you do a SHOW CREATE TABLE for each table please, and maybe dump a few rows of each
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    unpossible?
    I thought the Yanks had the monopoloy on the corruptification of the English language
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    459
    Rep Power
    80
    Code:
    SELECT DISTINCT product_colors.product_id, product_colors.color_desc, product_sizes.product_id, product_sizes.size_desc
    FROM product_colors
    INNER JOIN product_sizes
    ON product_colors.product_id = product_sizes.product_id
    product_id color_desc product_id size_desc
    1 Green 1 Small
    2 Red 2 Large
    2 Green 2 Large
    2 Red 2 Small
    2 Green 2 Small
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo