Applying product attributes

Question

I have a basic product table in my database

The table records most of the value of a product common to every product like the price, mrp, name, sku etc.

Now every product has a few different attributes not applicable to each product. A shirt has size and colors, a monitor or cell phone has different display resolution, a book has ISBN and an author name so on and so forth

I need to have these attributes as well. Can anyone please give me a hint on how it can be done.

I have gone through a few question on stackoverflow and google but could not figure out how to do it right or else I wouldn't risk myself being down voted on stackoverflow.

Answer 1

What I usually do, despite not being the best option, is to define a Varchar field called attributes which is a json with the data I want.

Of course, this is only useful if you are accessing data from a programming language. Keep in mind that this way you won't be able to make joins on those attributes.

Another more database-oriented possibility is to define a item type, then for each type define an attributes table. For example T-Shirts are type 0 then you have something like 0_attributes (or t_shirt_attributes).

A major disadvantage is that you won't be able to retrieve every item and its attributes in a single query.

Answer 2

Plan A:

Step 1. Decide on which fields will be used for searching or sorting.

Step 2. Shrink the list from Step 1 down to no more than, say, 5 fields. Pick the most useful.

Step 3. Create a table with those 5 fields, plus one TEXT field for a JSON collection of all the miscellany data about the items. (It may as well include the 5.)

Step 4. Build composite indexes that will handle many (but probably not all) of your common queries.

More discussion -- mostly about why EAV is bad.

Plan B:

Convert to MariaDB and use its "Dynamic columns" and other techniques, including indexing fields in JSON text.

Answer 3

That's very hard problem to solve. In fact it touches the essence of data modeling where there are multiple potential solutions but not necessarily all are equally good. Which one is the best depends on your actual data - how many different subsets of products there are, how many attributes are shared between subsets, how many are not, what will be the usage scenario (will your query access all products or only subsets of similar products?), etc.

So, some options are:

1. Just one, sparsely populated table with all attributes, with NULLs for attributes that are non-applicable for a given product. Potentially with set of views (one per subset) on top that limit both records and attributes valid for a given subset of products.

2. One main table for common attributes and dedicated tables for each subset - a kind of normalization applied, so you avoid NULLs. A set of views on top that join main table with a dedicated table for a given subset of products. This approach is somehow similar to super-class (main table) and sub-classes (dedicated tables) in OOP.

3. EAV (no!)

4. One main table with common attributes + set of generic attributes (ATTR_TYPE_1, ATTR_VAL_1), ... , (ATTR_TYPE_N, ATTR_VAL_N). This assumes same data type for generic attributes. ATTR_TYPE_# stores information what attribute it is for a given record (for instance: COLOR) and ATTR_VAL_# stores actual value (for instance: BLACK).

5. Similar to 4. but instead of generic attributes a field of XML type (or JSON) that stores all non-shared attributes. Applicability of this solution depends on your database support for such data types.

6. Analysis of how your non-shared attributes can be generalized (look both at attribute names and domains), so they became shared-ones (or at least shared by more subsets). For instance: ISBN or UPC/EAN are specific, PRODUCT_CODE is more general; MANUFACTURER or AUTHOR are specific, MAKER is more general; SIZE and RESOLUTION might both be called SIZE; the same would apply probably for TYPE, SEGMENT, VARIANT - they are probably all kind of TYPE, etc. Based on this analysis you create a data model with pretty generic column names like SIZE, FORM, TYPE, GROUP, etc. but the actual meaning is known from the context (subset that is actually queried): TYPE for shirts is T-shirt or long sleeve, TYPE for a book is novel or dictionary, TYPE for monitor is LCD or CRT, etc.

7. Divestiture of your company (focus only on one type of products, so the requirements for your data model are much simpler). ;-)