4

While designing an online shop database I had a problem with one part, could please someone give me an advise whether it is designed ok? The idea is that there are products which can be assigned to categories. Each category has a set of parameters for an easier search of products by customers. For example, a category "CPU" will have parameters "cpu family", "cpu speed" and category "HDD" will have "form factor" and "spindle speed". These parameter names are stored in the table tbCatParam and values of parameters for each product are in the table tbProdCatParam.

My schema is available here as jpeg: http://cid-ce0a6f63806e1ea9.office.live.com/self.aspx/PuclicFolder/scheme.jpg

Will it work like this? Many thanks

Darren
  • 68,902
  • 24
  • 138
  • 144
lekso
  • 1,731
  • 3
  • 24
  • 46
  • There's no reason the schema you have will not "work" to capture the data you've described. The real questions of performance, scalability, etc are things you can answer down the line. Properly decouple your code and you shouldn't have too much trouble adjusting to any needed schema changes. – Jeff Swensen Feb 17 '11 at 02:03
  • thank you. This design seems to work but exactly as you write, before going further I wanted to ask about performance issues, space saving etc. You recon, this design will have to be changed if there'll be lots of records? Why is it so and what can be a better way to do this? This seems like a trivial task and this is why I am a bit embarrassed. – lekso Feb 19 '11 at 00:50
  • When I try to follow the link, I am told that the resource could not be found. From your your description, the product parameters and values appear to be dynamic. This can be extremely flexible. But have you tried to turn this structure into useable information on screens and in reports this might be so difficult as to discourage you. – Walter Mitty Feb 14 '19 at 11:58

1 Answers1

2

If woul be better if you have no relation between tables TbProdCatParam and TbProdCat, for its recursive. You are relating TbProdCat and TbCategories, wich is related with tbCatParam. Thus, if the tbCategories has parameters, makes no sense you create another NxN relation between tbCatParam and tbProdCat, wich is a NxN table for itself.

This way, you even not need the table tbProdCatParam (unless you need paramCategories in both products and categories, but makes no sense).

Alex
  • 3,325
  • 11
  • 52
  • 80
  • what I have a problem with is that in tbCatParam I store a set of parameter names relevant for a particular category (like "cpu speed" and "cpu model") and in tbProdCatParam store the values for those parameters for each product assigned to a specific Category. Where else could I store those values other than in tbProdCatParam? If I delete connection between tbProdCatParam and tbCatParam then my parameter values won't relate to any parameter from tbCatparam and would be a mess. I do see problem in this schema, but don't know how to re-design it properly. – lekso Feb 17 '11 at 14:45
  • Well, you should ask "how many" for all the relations, to help you understand. For I saw, one product would has many categories and a category would has many products. So, we have NxN relation (with tbProdCat), it's ok. The problem seems to be a concept about parameters and categories: These categories, are categories of parameters? And these parameters is about product specifications? Could you give an example of a information that must to be stored in the BD? – Alex Feb 17 '11 at 18:29
  • Hi, yes this question is about general concept. And yes, those parameters are specifications of products and I thought that the best solution is to store product specifications for each category separately in order to facilitate classification/selection of products within same category. This is similar to eBay display concept. You can list there an Intel E7400 CPU in category "Intel CPUs" or "Other desktop PC parts" and while browsing either of these categories you'll see on the right side of screen different selection criteria for filtering out only those products that you'd like to look at – lekso Feb 17 '11 at 22:54
  • ..more.. so you asked for an example. Continuing with CPUs, in tbCatParam I store what specifications should be filled out in particular category (such as "Manufacturer" and "Ghz"), while tbProdCatParam is thought for storing actual values of those parameters, i.e. for Product1: "AMD" and "2.8" and for Product2 "Intel" and "3.2", etc. Then while browsing the "CPUs" category in online shop a customer would be able to quickly filter out all AMD processors or processors faster than "3Ghz". There should be some more efficient architecture than mine but I got stuck on this task. Thanks for help! – lekso Feb 17 '11 at 23:03
  • Well, I keep thinking that you musnt't have the relation between tbProdCat and tbCatParam, using tbProdCatParam. In mine conception, you should have a product, like a certain processor. This product belongs to two categories, for example. This categories ought to be "Intel products" and "Desktop processors". It may have one or more parameters, like (in "Desktop processors") "clock", "FSB", "number of cores" and "nanometers". So, you would store the information in a column in the table itself. Imagine three fields: param_ID, param_name, param_value [1, "Cores", 4]. Continue... – Alex Feb 18 '11 at 11:19
  • You could have a NxN relation between Parameters and Categories (obviously, with a table between), for a category would has one or more parameters and a parameter would has one or more categories (like "number of cores" relating to "desktop processors" and "notebook processors" for example). I think that's the fittest way to design your system's database. I hope I've helped. – Alex Feb 18 '11 at 11:27
  • thank you much for your help. Ok if I store values in tbCatParam (as per current schema) and make a third table. But still I'll need to somehow point the parameter value to a product. I can do this either by linking tbCatParam directly to Products (by adding field product_id to tbCatParam and creating 1xN relationship), or again link the new mid table to tbProdCat, this time table will have a composite key of four fields. Both ways have disadvantages. Why is in current schema the link via tbProdCatParam a problem? ..more.. – lekso Feb 19 '11 at 00:42
  • ... I thought to delete the relationship between tbProdCatParam and tbCatParam, then make AutoNumber primary key in tbProdCatParam instead of the current composite key, but what would this change give me other then necessity for additional verification of input in tbProdCatParam? – lekso Feb 19 '11 at 00:43
  • Why do you need a link between a parameter and a product? In my understanding, if you have categories to a product, and parameters are related to categories, implicitly you have parameters to products. Unless you must have a kind of parameters directly related to products, without a category (what I think no makes sense). Then, you may talking about another kind of entity, like "features". Features would be part of a product, not part of a category. In this case "features" and parameters will be different things. Decide what is the best to well describe your system (continue...) – Alex Feb 21 '11 at 13:24
  • I'm wondering what you need in your system, and you should implement a "features" table, in a relation one-to-many between the products table. In features for example, you would have the products data itself, like [serial number], [entry in stock date], [cost price], [sale price], etc. Even the features related on parameters would be stored here. Note that this kind of information is different from parameters of categories. If you should have many "features" set to a product and a "feature" would has more than one product, implement a NxN relation using another table. – Alex Feb 21 '11 at 13:34