Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012

    Question Unanswered: design for product options

    I am currently working on a new eCommerce system. Client requires a way to link products such that one product can be bundled with a number of other products. For example if Ford has a number of models, each may have a number of extras: Ex The Focus might have leather seats or cloth seats and it may have steel wheels and alloy wheels etc etc.

    I was thinking of three ways this could be handled:

    Separate table linked to the product table (through a manager class) which will contain all the possibilities of a category for a single product in one row. Thus, the header would be something like:
    ProductSKU, Category, OptionsSKUs
    Sample data:
    Pers_Focus2012_01, Seats, Focus_Cloth|Focus_Leather|Focus_Sport
    Pers_Focus2012_01, Wheels, Steel15_01|Alloy16_01|Alloy16_02|AlloyRS_01
    Comm_Transit2012_01, Seats, Transit_Cloth|Transit_Leather

    When accessing these a method will be required to generate Iterators for each category and be able to show the contents on the front end + generate a complete basket order showing all the options selected.

    A separate table with its own manager class which would be used to extract the contents of each of the categories. One row would contain one category option per product. Header would be: ProductSKU, Category, OptionSKU
    Sample data:
    Pers_Focus2012_01, Seats, Focus_Cloth
    Pers_Focus2012_01, Seats, Focus_Leather
    Pers_Focus2012_01, Seats, Focus_Sport
    Pers_Focus2012_01, Wheels, Steel15_01
    Pers_Focus2012_01, Wheels, Alloy16_01
    Pers_Focus2012_01, Wheels, Alloy16_02
    Comm_Transit2012_01, Seats, Transit_Cloth
    Comm_Transit2012_01, Seats, Transit_Leather

    This will make it easier to extract data and will be able to use Oracle caching which should improve performance. Also, in case manual adjustments are required, it is also easier to extract data through pl/sql.

    Extending the product table with clob in which I could add an xml definition of the options list eg:

    This would require extensive parsing of the xml and would increase the size of the product table which i think is not the best idea. Also the options list needs to be updated on a regular basis (there will be a product import option).

    As a summary I suspect option 1 is probably the easier to implement but it requires a bit more processing to be able to display the data although I dont think its significant enough since there is not a huge number of products. However, I think option 2 is the best all rounder since its not much harder to implement than OPt 1 and allows for much more customization.

    Please feed in your opinions, maybe even other ways this could be carried out?

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Design table data to Third Normal Form.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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