Thread: design for product options
06-28-12, 03:17 #1Registered User
- Join Date
- Jun 2012
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
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
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?
06-28-12, 08:41 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
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.