Product with Multiple Category Type Database Schema

product with multiple category type database schema

The instrument and the artist are both examples of "has-a" relationships. The genre is an example of an "is-a" relationship. Has-a and Is-a relationships are modeled quite differently in relational database design. A song is a pop song, or a song is a rock song. (I presume that a song can be both pop and rock, but that's not clear in your description).

Has-a relationships are more common, and are generally covered in any good tutorial. Is-a relationships are often not given enough coverage in those same tutorials. If you want to get some articles about ER modeling of is-a relationships, lookup "generalization/specialization". This will explain how to depict one of these relationships but not how to design tables to fit.

If you are familiar with object modeling, your easiest handle on the concept is known as class/subclass pattern aka type/subtype. This is straightforward, but it depends on built in support for inheritance. This support is generally present in any object system, and does most of the hard work for you. If you are designing a relational database you come face to face with the fact that the relational model does not directly support inheritance. This means that you have to design tables that mimic the benefits of inheritance, unless you are willing to use SQL extensions available in your dialect of SQL.

Two relatively standard designs are known by the names of these two tags:
single-table-inheritance
class-table-inheritance

You can get a brief description by reading the Info tab of each of these tags. You can get an even better description by looking up Martin Fowler's treatment of these two patterns.

The single table solution will help you create a single table for all song files, with columns that pertain to any or all of the categories.

The class table solution will help you design one table for the song files and one table for each genre. It will be helpful if you use the shared primary key technique and thereby cause each entry in a genre table to "inherit" its ID from the corresponding entry in the song file table. You have to make this inheritance happen by extra programming when you go to insert a new song file.

Which one is better? It depends on your case. You decide.

Database design for site with many product categories

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.

Database schema for multiple category of product with different attributes

There are attributes common to all products (e.g. price and stock etc.).

Create a table for this with an appropriate real life key e.g. EAN (consider using a third party data source for this).

But how do I manage attributes specific to each product within its category? ... if I wanted to sell books in my website I would need to add a books table

Yes and the attributes specific to books would go here. Remember to create a reference to your 'attributes common to all products' table e.g. ensure the EAN correcponds to an valid ISBN. Also consider that some book attributes might be common to other similar products, so you could have various subtypes modelled in your database.

Best way to make multiple categories item database structure

Found that Nested Set Model is the best approach to deal with hierarchical data

---

Related Topics