5

I'm having some difficult designing a database with a lot of different attributes per product.

Here's the situation: each product should belong to a category and each category has a different set of attributes.

E.g.:

  • Product iGal 7S → Category Smartphones → Attributes attr_phone
  • Product UberLaptop → Category Notebooks & Laptops → Attributes attr_laptop

From what I understood, it would be best to avoid EAV and proceed with class table inheritance.

On this observation, I would have as starting point:

Starting design

If I had a single attributes set, I'd use an attribute_id foreign key in the products table and call it a day, but I'll potentially have 50+ attributes set (and therefore 50+ tables).

  1. Is CTI still valid as approach?
  2. How could I assign the correct attributes set to a product?

Sorry for the stupid question, but in particular I fail to understand how I can assign a different attributes set when a different category is selected. Thanks for reading.

Community
  • 1
  • 1
Bacco
  • 143
  • 1
  • 1
  • 5

2 Answers2

1

Is CTI still valid as approach?

I think that if the number of the categories are in the order of tenth, and not of hundredth, then yes.

How could I assign the correct attributes set to a product?

You could add to each category row the table name of corresponding table of attributes, and for each attribute table, the id of the row will be the id of the corresponding product (so that you can define it as a foreign key for the products table).

Renzo
  • 26,848
  • 5
  • 49
  • 61
0

In almost all situations it is "wrong" to have 55 tables with identical schema. Making it 1 table is better. But then it gets you into the nightmare called "Entity-Attribute-Value".

Pick a few "attributes" that you usually need to search on. Put the rest into a JSON string in a single column. More details

Your schema is not quite EAV. It is an interesting variant; what does it stand for? I don't think I have seen this as an alternative to EAV. I don't yet have an opinion on whether this replaces the set of problems that EAV has with a different set of problems. Or maybe it is better.

What client language will you be using? You will need to turn category.name into attr_laptop in order to SELECT ... from attr_laptop ... This implies dynamically creating the queries. (This is quite easy in most client languages. It is also possible, though a little clumsy, in Stored Routines.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I have a table named "Product" which has different category types i.e. Compressor, Condenser etc.[total categories minimum 30]. Now Compressors has attributes like "Family,Type,Volts" and condensers have attributes like "Height,Width,Depth". And out of 30 categories may be only 10 categories has attributes. So what is the best way to store attributes in "Product" table. Should I make Columns for each attributes (30-40 columns). OR how can I make it dynamic if I want to add attributes later. and store in JSON as you said? and where can we store this JSON string -in "Product" table.? – G_real Feb 11 '19 at 16:48
  • @VirenPanchal - I added to my Answer. For a 'laptop', the JSON would have color, os, hdmi, but not refillable, etc. As for 30-40 columns, "no". Tomorrow you will need 45. – Rick James Feb 11 '19 at 23:26