SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database schema for shopping cart

    Hi, I am trying to set out some shopping cart tables where prices may on some products relate to all sizes and colours and for others, the price may be specific to size or color (or other product-specific value)

    I may have confused my plan, in another thread, with my eg values.

    necessary to clarify: the attribute name column would store just one value in all rows. that value might be color or size or description or unit of measurement (as examples).

    Then, in the attribute_values column, it would store one value, which relates 1:1 with the attrubute name col.

    And the unit of measuremnt would relate 1:1 with the attribute value col. (or it would be null if no unit of measurement applied).

    Having shown columns for relating the price to an overall product eg all shirts whatever their colour, where should I store the price when it would be size dependant?



    Code MySQL:
     
    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
    At the risk of this being an EAV model and to be avoided unless necessary, what other way should I structure my tables?

    The only other way I have managed to get my head around is (I think), unnecessarily table heavy.
    tables each for:
    products
    | id | product_name |
    product_colours
    | id | product_id | colour |
    product_sizes
    | id | product_id | size | unit_of_measurement |
    product_weights
    | id | product_id | weight | unit_of_measurement |
    product_descriptions
    | id | product_id | desc | short_long |
    product_images
    | id | product_id | image |
    product_prices
    | id | product_id | price |

    But then how should I relate a price to a product such as shampoo, where different sizes are different prices? and if price was size dependant and colour-dependant, would I need a m2m table for each such variation?

    I can't help but think I am over thinking this somewhat so I need some help please.

    bazz

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This is the pattern I used when I last worked on a eCommerce system that supported varying attributes across line of products that change in price. While it is relatively complex it is very flexible and generic enough to be used to support most types of products. In short the you need to think about a "product" as being a container. Then what I term "part numbers" are the physical items. A product (container) can have multiple attributes and each attribute can have multiple option values. The option values are than associated with a part number via a standard m:n relationship with a look-up table.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I knew I had written a nice example somewhere of the pattern and test data.

    Product Schema Example
    The only code I hate more than my own is everyone else's.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much oddz.

    Some of my products will have the price related to the variations eg sizes of shampoo. others will have a standard price for all variations eg shoes.

    So would you recommend that price should be stored in the part_numbers_to_products_options_values table OR should it be an FK to a prices table?

    I am just struggling to get my head around the best approach to make price storage consistent between products that have a standard price across all size/color variations (eg shoes), and those which are specific to a size (eg shampoo).

    bazz


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
  •