What is the right approach for storing different types of product data

Question

I'm creating an application that stores product data in a (now) relational database. But a washing machine has different properties then a laptop fe. They both are a type of product, with always a price, title, desc and image, but only laptops have a harddisk (mostly)

So what to choose as a model?

1. key=> value metatable with megajoins.
2. everything in one table.
3. create one product table and a joined table for each category.
4. The unicorn quality solution I didn't know about.

I'm favouring option 3, but that still feels wrong and hard to scale and I'm unsure if I'm forgetting something or that my years of relational php / mysql thinking are steering me away from another and way better choice.

Answer 1

You need a kind of key/value tables to solve this relationally.

But there are some things to consider. A washing machine may have 1200 or 1400 rpm but certainly not 367. So there are some values that must be picked from lists, whereas others my be free text. Then there are probably values that are obligatory and others that are not, e.g. a computer that may come with a keyboard (which you could further specify) or not.

But let's say there are only value list properties (no free text) and for optional properties you have an entry 'none' in the list and for obligatory not. That means, however, that suddenly all properties of a category are "obligatory" (i.e. for a PC you must say what keyboard, even if it is 'none', whereas for a chair you must not).

This leads to something like:

table category

category_id   text  
c1            PC
c2            Washing machine

table products

product_id   category_id   text           ...
p1           c2            Washy WM1000
p2           c1            Super PC

table properties

property_id   text
100           rpm
200           keyboard

table property_values

value_id   property_id   text
21         100           1200
22         100           1400
23         200           with numpad
24         200           without numpad
25         200           none

category_properties

category_id   property_id
c1            200
c2            100

product_values

product_id   value_id
p1           22
p2           23