Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Post Review Product/Size/Color/Price Ecommerce Database...

    Hello...

    Hello guys... I´m designing an ecommerce database...

    * A product can have several sizes and several colors
    * Each combination of products´s size/color must have a specific price

    So, I can have a Product X with sizes: A,B,C and colors: Green,Black and White

    And each combination have its price...

    It´s implemented in Amazon, one example here : Amazon LINK Sample

    What I´ve done so far: Click image for larger version. 

Name:	product.png 
Views:	240 
Size:	17.6 KB 
ID:	11279

    What you guys think? Suggestions?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are all products available in all sizes and all colours?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    Are all products available in all sizes and all colours?
    Hi...

    No... It can be different...
    In some cases the product has no color or size at all...

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ta.

    There are these two rows in the database:
    Code:
    ProductSize
    Id     ProductFk     SizeFk
    1      1             1
    
    ColorProduct
    Id     ProductFk     ColorFk
    1000   1000          1
    Please could you explain if it is valid for me to enter 1 for SizeProductFk and 1000 for ColorProductFk? If it is not, then what is there to stop me?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2010
    Posts
    6

    Substancesundonte

    Quote Originally Posted by pootle flump View Post
    Ta.

    There are these two rows in the database:
    Code:
    ProductSize
    Id     ProductFk     SizeFk
    1      1             1
    
    ColorProduct
    Id     ProductFk     ColorFk
    1000   1000          1
    Please could you explain if it is valid for me to enter 1 for SizeProductFk and 1000 for ColorProductFk? If it is not, then what is there to stop me?
    If there is a Product with ID 1000 and 1, yes should be valid...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to be sure you understand what I am saying:
    It is correct to have a row in ProductPrice that references a row in ProductSize for a product of ID 1 and that also references a row in ProductColor for a product of ID 1000?

    In other words, a row in ProductPrice can reference one product for the size and another product for the coloru?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    Just to be sure you understand what I am saying:
    It is correct to have a row in ProductPrice that references a row in ProductSize for a product of ID 1 and that also references a row in ProductColor for a product of ID 1000?

    In other words, a row in ProductPrice can reference one product for the size and another product for the coloru?
    I understood now...
    No, It should never happen ...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    "Should", "could" and "oh sh*t, it has" are all very different beasties.
    So - any ideas how to stop it happening? I'm talking declaratively, by properly constraining the database, not "ah, the app will check....".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2010
    Posts
    6
    Quote Originally Posted by pootle flump View Post
    "Should", "could" and "oh sh*t, it has" are all very different beasties.
    So - any ideas how to stop it happening? I'm talking declaratively, by properly constraining the database, not "ah, the app will check....".
    I got your point, but I found other problens that I must fix before setting constraints...

    1 - Analysing my design how can I have one ProductPrice without color AND without size ?
    2 - What you think change my design to something like that :
    • A Product table that will contain a record for every combination of item, size, and colour, with a unique SKU and Price for each.
    • A Size table that normalizes out the common sizes Products may have. Product has a foreign key to this table.
    • A Colour table, as above.


    Thanks

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    either create an entry in the size table for "all sizes"
    OR
    carry a default price at the product level, if no price quoted at the colour/size entries then use the price at product level.

    the second approach requires additional programming input and development to make sure you pick the right price. it also means that the users have to be more careful about where and how they manipulate the price element. it would be all to easy adjust the wrong price, not appreciating that there may be multiple prices for that product line
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2010
    Posts
    6
    Quote Originally Posted by healdem View Post
    either create an entry in the size table for "all sizes"
    OR
    carry a default price at the product level, if no price quoted at the colour/size entries then use the price at product level.

    the second approach requires additional programming input and development to make sure you pick the right price. it also means that the users have to be more careful about where and how they manipulate the price element. it would be all to easy adjust the wrong price, not appreciating that there may be multiple prices for that product line
    Thanks!
    So, You think I should stay with that database design (Original post) ?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    No
    Im saying you need to analyse your business requirements and develop a strategy that allows you to have products, with different colours, different sizes, which seemingly also have different prices. how you do that is up to you. sadly in the development world theres often many answers and more than one that is right, and more than one that is workable.

    the issue is how you design your schema such that it supports the business requirement.

    its arguable that the real issue is how you define the product
    is the product, say, a specific style / cut / fabric of pair of trousers and then sub classified by colour and size,
    or
    is the product a pair of chinos in navy blue waist 32, leg 29, with belt, and say wasit 24 is another product.
    to me thats the nub of your problem.
    persoanlly I'd expedct it to be the latter, I'd be surprised if any retailing outlet saw a product as a pair of Chinos, and then dropped down a level to sub classify them.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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