Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Apr 2010
    Posts
    53

    Ecommerce Website Design

    I'm hoping to scrounge some free crumbs of advice from you good people at dbforums:

    I'm designing a ecommerce system for a customer.
    They wish to add various products to the site.
    A product may or may not have one or more options.
    Any given option will have one or more values.
    The value of an option may effect the price of a given product.

    So for example they wish to add a duvay cover (they sell beds, bed linen, etc.)
    They need to be able to add a colour option to this, and the colour option must be able to, for example, have the following values: blue, red, green, yellow, etc.
    The duvay cover also needs to have a size option which will have the following values: small, double, king-size.
    The colour option will not effect the price but the size option does.

    I thought i could model this as follows:

    product table: (this contains the products)
    product_id, name, price
    1, duvay cover, 50

    option table: (this contains options that can be assigned to products)
    option_id, option
    1, colour
    2, size

    product_options table: (this links products to options)
    product_id, option_id
    1, 1
    1, 2

    option_values table: (this defines the values an option can have)
    option_value_id, option_id, value
    1, 1, blue
    2, 1, red
    3, 1, green
    4, 1, yellow
    5, 2, small
    6, 2, double
    7, 2, king-size

    product_value_data table: (this defines what affect a selection of an option value has - at the moment just price adjustment)
    product_id, option_value_id, price_adjust
    1, 6, +15
    1, 7, +20

    so here choosing the double size adds 15 and choosing the king-size adds 20 to the standard price...

    Any thoughts, criticisms, ideas, etc. would be very welcome....

    Many Thanks

    j

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would do it like this --

    product table: (this contains the products)
    product_id, name, price, colour, size
    1, duvay cover, 50, blue, small
    2, duvay cover, 65, blue, double
    3, duvay cover, 70, blue, king-size
    4, duvay cover, 50, red, small
    5, duvay cover, 65, red, double
    etc.

    why make things harder than they have to be?

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

  3. #3
    Join Date
    Apr 2010
    Posts
    53
    hi rudy, thanks for the reply..

    - wont that lead to an awful lot of repetition? i mean the product name would be repeated for every possible combination of option values.

    Further not all products have the same option choices so there would be a lot of null columns

    Also adding a new option would mean, altering the table by adding new columns rather than just adding rows to an exiting table...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    wont that lead to an awful lot of repetition?
    this depends entirely on your definition of awful

    where do you think this repetition lies on a detrimental scale ranging from a negligible piffle on one end to having to explain late delivery to the client because application testing isn't finished at the other end?

    in any case you might want to evaluate how this repetition will affect you

    you can immediately see (i hope) the advantage of this simple design in terms of query construction -- no joins means easier queries

    where does it hurt? perhaps in disk space

    how many rows are there going to be? a few thousand? only if you expect a few billion should space matter

    Quote Originally Posted by jx12345 View Post
    i mean the product name would be repeated for every possible combination of option values
    not possible combinations, only actual combinations, i.e. if you don't carry green double, then there's no row for that

    Quote Originally Posted by jx12345 View Post
    Further not all products have the same option choices so there would be a lot of null columns
    again, define "a lot"

    you didn't mention very many options besides colour and size, and i doubt a bedding store will have more than a dozen or two

    in any case, ask yourself again how nulls would impact you

    Quote Originally Posted by jx12345 View Post
    Also adding a new option would mean, altering the table by adding new columns rather than just adding rows to an exiting table...
    you are correct, but how often is this going to happen?

    besides, you're having questions with just colours and sizes, imagine how many more tables you'll need and how much more complex your joins will be when these unmentioned other options have to be added to the mix

    your choices: the bedding store adds another few options, you do a small update to the table and the application, versus the cost (in development time) to generalizing this using option tables

    i know which way i'd go

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

  5. #5
    Join Date
    Apr 2010
    Posts
    53
    Hi Rudy,

    Thanks again for taking the time to reply.

    Certainly I can see the advantage of the single table design you suggest, and there is certainly no concern regarding disk space.

    The problems I forsee with a simple single table design are as follows:

    1)
    The same option may be used for various products, for example, the colour option is applicable to duvay covers, pillow covers, mattress covers, etc.

    With the multi-table desing adding a colour to a new products, say cushion covers, will be as simple as inserting a record into the product_options table, with a single table design a record for each colour the product is available in would have to be added. - I'm just imaging all sorts of problems here - the person administering it types in the name or price wrong on one of the multiple records they would have to add.

    Further, if a new colour is added, or a colour is removed or replaced this can simply be added, removed, or update in one place, the option_values table and all the products that have the colour option will be 'available' in the new colour range. With the simple table design whole records would have to be inserted, updated or remove for each product that had a colour.

    2)
    When displaying the products and the option choices with the simple table desing i imagine i'd have to check losts of query results to see if they came back null in order to decide whether to display them or not.

    Any thoughts much welcome..

    thanks

    jim

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    1)
    The same option may be used for various products, for example, the colour option is applicable to duvay covers, pillow covers, mattress covers, etc.
    this isn't a problem with the single table design

    products where colour makes sense will have a value in the colour column, and products where it doesn't won't


    Quote Originally Posted by jx12345 View Post
    I'm just imaging all sorts of problems here - the person administering it types in the name or price wrong on one of the multiple records they would have to add.
    this is where your application user interface comes into play -- to add a bunch of products which all have the same description, same price, same vendor, etc. etc., but only differ in colour, you would design the input form so that they enter the common stuff only once, and then check off the colours

    Quote Originally Posted by jx12345 View Post
    ... and all the products that have the colour option will be 'available' in the new colour range.
    again, be careful with this, you may find that by adding "chartreuse" to pillow covers also makes it "available" to products which actually don't come in that colour

    Quote Originally Posted by jx12345 View Post
    2)
    When displaying the products and the option choices with the simple table desing i imagine i'd have to check losts of query results ...
    no

    there is only one table, so there is only one query, so you print the columns that have values, and you're done
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2010
    Posts
    53
    hmmm... I see what you're saying, but those null fields seem wrong. - isn't it against one of the normal forms or something to have fields that are just not applicable to the item represented by the primary key in a row?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    hmmm... I see what you're saying, but those null fields seem wrong.
    i know one good way for you to learn exactly how "wrong" they are

    what i'd like you to try is to create the single table, write the single query needed to access it, and display the information on your web site, mindful of the NULLs which won't actually display

    that really shouldn't take too long, agreed?

    okay, next, i'd like you to go ahead and develop your version with all the option tables and option value tables and so on

    after you're finished, which might take a fair bit longer (agreed?), i'd like you to come back and give your assessment of which approach was (a) harder, and (b) more aesthetically pleasing from a normal forms point of view

    note: please answer (b) only after you've developed both versions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2010
    Posts
    53
    lol... ok, i will... might be a while before i post back...

    thanks.

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    A caveat to Rudy's suggestion: it's much harder, once you have actual data in the system, to normalize than to denormalize.

    That is to say, if you find you have integrity problems down the road, and want to solve them by normalizing, the denormalized data probably won't fit the new normalized model.

    For example, Rudy says you can just leave color blank "when it's not needed." Sounds fine, but your data entry folks will *not* think the way you do nor will they think the way each other do, that's I can guarantee you. So these denormalized columns won't be used in a consistent manner.

    If you never get beyond a few hundred items, it's not a problem. If you wind up with thousands of entries, it will be a major task to go through them and change the data and verify that you haven't changed the meaning.

    That's if you *can* change the data. If it has to match invoices you've already sent to customers, you may be stuck with it...

    I'm not saying everything has to be perfectly normalized, or that any design can anticipate every possible change. Just that a few hours of thought now, rather than doing the quick and dirty hack, might save you a lot of frustration down the road.

    But, then, maybe that frustration will translate into a lot of billable hours. Maybe Rudy's on to something.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    heh, that's cute, but no, i never give out counter-productive advice in the hope of snagging some consulting hours, that's not my game at all

    in this instance i honestly believe the bedding business is not complicated enough to warrant option tables

    as for users using the colour column when it's not applicable, if you do a good job on the user interface, they won't get that chance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2010
    Posts
    53
    Thanks for the further input, (just taking a break from trying to implement both versions) i just have a couple of quick queries:

    1) Rudy, you mentioned a couple of times the importance of a good user interface in conjunction with the simple table design - am i not just making up for a badly designed data structure by using the user interface to control the users inputs?

    2) Would you consider the option & option_values tables design i originally suggested to be a type of Entity-Attribute-Value model?

    Thanks again

    j

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    am i not just making up for a badly designed data structure by using the user interface to control the users inputs?
    not really

    if you do have a multi-table fully generalized database backend, do you somehow imagine that this will make your user interface simpler?

    okay, assume the user interface is one simple form...

    wouldn't that form begin to resemble the single table design?

    also, if you don't do any user input editing in the application layer, what kind of error messages are you going to generate when you get a foreign key error on one of your option tables?



    Quote Originally Posted by jx12345 View Post
    2) Would you consider the option & option_values tables design i originally suggested to be a type of Entity-Attribute-Value model?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2010
    Posts
    53
    Quote Originally Posted by r937 View Post
    not really

    if you do have a multi-table fully generalized database backend, do you somehow imagine that this will make your user interface simpler?
    yeah, i kind of do at the moment, (although i haven't finished implementing either design yet so i may be very wrong...)

    ok, this is what i'm thinking (this is going to stray into the implementation so forgive me if its goes a bit off the Database Concepts & Design topic):

    a user wants to add a new product.

    I imagine the single-table design working as follows:

    The user adds data that is going to be common to all optional variations of the product such as its name & description into a couple of text boxes.

    They then have a series of textareas, one for each of the possible options where they can add a list of options, for example, there's one for colour options, one for size options etc.

    In order to input this data into the database i'll have to explode the option values into arrays and loop through them inserting a line for all possible variations, so for example i'll have:

    1, duvay cover, 50, blue, small
    2, duvay cover, 65, blue, double
    3, duvay cover, 70, blue, king-size
    4, duvay cover, 50, red, small
    5, duvay cover, 65, red, double
    ...etc..
    Whereas:
    I imagine the multi-table design working as follows:

    The user adds data that is going to be common to all optional variations of the product such as its name & description into a couple of text boxes.

    But then simply ticks or unticks a box to select which of the options (pulled from the options table) are applicable to the new product.

    In order to input this data I insert the product data into the product table and insert the relevant data into the product-option table depending on what options have been selected.


    That seems easier both for the end user/admin and from a coding point of view? no?


    Many thanks again for taking the time to read & reply

    j

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jx12345 View Post
    But then simply ticks or unticks a box to select which of the options (pulled from the options table) are applicable to the new product.
    now i want you to take a leap of faith

    what would you need to do in order to present the user with checkboxes but assuming you were still targetting the new product to be stored into the single table?

    i promise, this line of thought is actually leading somewhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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