May 8, 2009 at 4:32 pm
Hi all, I'm in dire need of database design help so I'm here to ask the SSC borg.
My current project is to design a product architecture that can accomodate custom products meaning my company manufactures postcards that are custom made to order, not necessarily something that you just pick out of a catalog and order. So building a product table is not as easy as having a product record and a price.
If you have experience in this and would like to share the knowledge, then I would assimilate with the borg and send my appreciation as well.
Thanks,
Tung D.
Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip
May 9, 2009 at 5:24 am
Hi Tung
I'm not sure if I got you but if you try to find a way to store any custom information about your products within your database design I would suggest one of the following:
EAV
The EAV (entity-attribute-value) design is the most flexible design for databases because it can hold any kind of information.
Issue
The queries for this database design become very huge and the performance is not very good. Another problem are mandatory fields. There is no way to ensure some mandatory product information within the class tables.
XML
If you add a XML column to your product table you can store any information within. You can add an XML index for better performance. If you usually only need to store and get the data depending on other query criteria (customer, product-id, ...) I would prefer this design.
Issue
If you frequently need to query the custom information within the XML column and you have many products the queries are also slow.
Categories
If you have any idea about the data to be stored I would suggest a product table with the main product information and some property tables by categories (in case of postcards maybe formats, colors, motives, ...). The database will be a correct relational database which should perform very good.
Issue
It is not as flexible as the other approaches.
Hope this helps!
Greets
Flo
May 11, 2009 at 9:45 am
I do this on steroids. I have a "traditional" table with ProductID, sku, and other cols that are common to ALL products like weight and dims.
I have a second tbl that handles the EAV stuff discussed above. Its simpliest form would have productID, valuename, value. In my case I futher want to contol the valueNames allowed so I normalize that out into a customvariables table to control them. Some valuesname have restricted sets of allowed values so those are further normalized into a customvalues tbl. This is good for handling sparse data.
The xml approach is good if you dont want to search on the embedded data and some higher-level code will generate/parse it. This also allows more flexibility for what the higher code puts there including hints that control its logic.
May 11, 2009 at 2:22 pm
I've been involved in a similar project where we used an EAV method. We had category and type tables and then defined the EAV values base don category and type. It was very flexible and great for the order entry system for the small manufacturer I worked for. In your case it would work something like:
Category - Photo Card
Category Attributes - Photo or photo path, Lenght, width, card stock basis weight, finish (glossy or matte).
We also had a structure that defined lists, defualts, and if the attriubutes where required for the attributes. I'll see if I can dig up the structure out the depths later if you like.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply