I want to be able to store different types of information for products in different categories.
So Category A maybe TV's and category B maybe DVD players, I want to be able to store different types of information on each product in that category. So for TV I may want to store screen size etc.. and for DVD players I may want to store if it supports upscaling etc.
Obviousely each peice of info will be of a different data type but the reason I ask how best to store this is because I will have lots of categories of products and the products will be used for filtering too. So I need to create a solution which is not too costly on performance.
Thanks in advance, any guidance even to any online tutorial would be appreciated.
Probably a long way from best, but maybe a simple parent-child relationship? With common product information in the parent table (name, product number, classification...,) and product specific information in the child table, along with the presentation order... (ParentID, PresentationOrder, parameter, parameter description...)
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
I want to be able to store different types of information on each product in that category.
So I need to create a solution which is not too costly on performance.
There are two basic ways to look at this kind of problem.
If you want to optimize performance for INSERT operations, you can stuff some XML into each row. This is utter anarchy from a data management perspective, but it sure is fast.
If you want to optimize SELECT, UPDATE, and DELETE operations, you can use entity decomposition. This means that each new class of objects needs a new table added to your schema, but it means that you can then manage the resulting data.
In the second instance, of creating parent-child relationships, we could end up with thousands of tables, is this really the way to do it? have hundreds of tables for category specific details? and you would not know how many fields/params are sufficient.
Looking around and speaking to people I have been told that another option is to have 1 related table to the categories which stores category related details and also another table related to that storing its type. Anybody know any more about this?
It sounds like thats the best way to do it, fortunately I using sql server so I can use that functionality.
I would be interesting to learn more about how to do this using another table which stores fields related to product and category. Is there anyway of validating the data and does anybody use this? sure people who use mysql to store the data must do this?