This is a known (anti) pattern called "Entity Attribute Value" (you can search for that name in the internet if you want to find out more).
Nowadays (and especially with Postgres) I would go for a JSONB column that stores the category specific attributes of each product rather than an additional fields table.
You can even go so far to validate the dynamic attributes in the product table based on the meta-information in the category table.
So something like this:
create table category
(
id integer primary key,
name varchar(50) not null,
allowed_attributes jsonb not null
);
create table product
(
id integer primary key,
name varchar(100) not null,
brand varchar(100) not null, -- that should probably be a foreign key
... other common columns ...
);
create table product_category
(
product_id integer not null references product,
category_id integer not null references category,
attributes jsonb not null, -- category specific attributes
primary key (product_id, category_id)
);
Now with the list of "allowed attributes" in the category table we can write a trigger that validates them.
First I create a little helper function that makes sure that all keys from one JSON value are present in another:
create function validate_attributes(p_allowed jsonb, p_to_check jsonb)
returns boolean
as
$$
select p_allowed ?& (select array_agg(k) from jsonb_object_keys(p_to_check) as t(k));
$$
language sql;
This function is then used in the trigger for the category table:
create function validate_category_trg()
returns trigger
as
$$
declare
l_allowed jsonb;
l_valid boolean;
begin
select allowed_attributes
into l_allowed
from category
where id = new.category_id;
l_valid := validate_attributes(l_allowed, new.attributes);
if l_valid = false then
raise 'some attributes are not allowed for that category';
end if;
return new;
end;
$$
language plpgsql;
Now let's insert some sample data:
insert into category (id, name, allowed_attributes)
values
(1, 'TV Set', '{"display_size": "number", "color": "string"}'::jsonb),
(2, 'Laptop', '{"ram_gb": "number", "display_size": "number"}');
insert into product (id, name)
values
(1, 'Big TV'),
(2, 'Small TV'),
(3, 'High-End Laptop');
And now let's insert the category information:
insert into product_category (product_id, category_id, attributes)
values
(1, 1, '{"display_size": 60}'), -- Big TV
(2, 1, '{"display_size": 32}'), -- Small TV
(3, 2, '{"ram_gb": 128}'); -- Laptop
This works as all attributes are defined in the category. If we tried to insert the following:
insert into product_category (product_id, category_id, attributes)
values
(3, 2, '{"usb_ports": 5}');
Then the trigger will throw an exception preventing use from inserting the row.
This can be extended to actually use the data type information stored in the allowed_attributes.
To find products based on attributes, we can use the JSON functions provided by Postgres, e.g. all products that have a display_size:
select p.*
from product p
where exists (select *
from product_category pc
where pc.product_id = p.id
and pc.attributes ? 'display_size');
Finding products that contain multiple attributes is just as easy (and a lot more complicated with the "traditional" EAV model).
The following query finds only products that have the attributes display_size and ram_gb
select p.*
from product p
where exists (select *
from product_category pc
where pc.product_id = p.id
and pc.attributes ?& '{display_size, ram_gb}');
This can be indexed quite efficiently to make searching faster.
I am not entirely sure you do want to store the attributes in the product_category table. Maybe they should be stored directly in the product table - but that depends on your requirements and how you want to manage them.
With the above approach you could e.g. have a category "Computer HW" that would store information like number of CPUs, RAM and clock speed. That category (and its attributes) could be used e.g. Smartphones and Laptops at the same time.
However you would need more than one row in product_category to fully describe a product if you do that.
The most common approach is probably to store the attributes directly on the product and skip all the dynamic JSONB validation.
So something like this:
create table category
(
id integer primary key,
name varchar(50) not null
);
create table product
(
id integer primary key,
name varchar(100) not null,
brand varchar(100) not null, -- that should probably be a foreign key
attributes jsonb not null,
... other common columns ...
);
create table product_category
(
product_id integer not null references product,
category_id integer not null references category,
primary key (product_id, category_id)
);
Or even a combination of both if you need category specific dynamic attributes and product specific attributes regardless of the category.
