PostgreSQL Database Design with Multiple Variables and Constraints

database-designpostgresql

I'm using PostgreSQL. I have the following entities Categories, Items, Attributes.

Relationships:

  • M2M between categories and items (an item can be in multiple categories, a category can have multiple items)

  • FK between Items and Attributes (an item can have multiple attributes)

  • M2M between Categories and Attributes (a category can have multiple attributes, an attribute can be find in multiple categories)

The design issues:

  • constraint: A category can have multiple attributes. An item can have multiple attributes, but only the ones linked to the categories in which the item is assigned.

The attributes have values. There are 2 types of attributes, integers or lists. Example:

Item attributes with integer values:

- length: 32

- strength: 44

The values is only dependent on the product.

Item attributes with string list values:

mode: full or mode: half, or mode: half,full

The values for string list attributes, like mode are a predefined list. An item that have the attribute mode can have one or many of the predefined values.

I want to use the attributes as advanced search:

in case of attributes with integer values(for items), check if the values if is between a minim and/or max value
It is similar to:

enter image description here
in case of attributes with values as a string list, check if at least one of the selected values in the search is also found in the attributes values for the product. It is similar to:

enter image description here

In a way similar to search filter on: ebay, but
mostly with min_max filters a less list checkbox filters.

I don't exactly know how to model the attributes connection and values, to keep them dynamic(add/remove attributes without changing the database structure)

I don't know in which category(es) a product will be, so adding attributes as columns I don't think is a viable option. Besides that some of them being lists add to the problem(I though about using json for their product values) but still maintain a sort of constraint regarding values available.

Best Answer

Here is one approach I use this model. I suggest being careful with attributes, do not turn everything into an attribute, I have dealt with DB models that use such an approach several times. It makes what should be simple query a mess, and means tracking types, and lots of casting to and from data types.

treat the below a psuedo code just to get the ball going on conversation 

create table item (
    item_id serial primary key,
    item_descrip text,
    item_uom_id int, --UOM = unit of Measure another table not described 
    item_list_price);

create table atts (
    atts_id serial primary key,
    atts_name char(55),
    att_value char(85),
    atts_notes text
);

create table cats (
    cats_id serial primary key,
    cats_name char(55),
    cats_notes text);

create table links_cat_item_atts (
    lcia_id serial primary key,
    lcia_type char(4),
    lcia_link_id  int,
    lcia_cat_id int default NULL,
    lcia_att_id int default NULL,
        CONSTRAINT link_type_con CHECK (lcia_link_id = 'ITEM' 
        OR lcia_link_id = 'CATS' OR lcia_link_id = 'ATTS')) ;

--to use this most simple quiers to figure out the what is linked to what 

Select * from item
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id 
    inner join atts on lcia_att_id = atts_id

-- to pull cats with attributes this 

Select * from cats
    inner join links_cat_item_atts on cat_id = lcia_link_id and lcia_type = 'CATS'
    inner join atts on lcia_att_id = atts_id

-- built a list of attributes that are for cats you can do this 

Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts 
                                    where  lcia_link_id = LIMIT_ID 
                                    and  lcia_type = 'CATS' )

--this holds true for items to build a list of attribiutes that are for specific item 
Select * from atts where atts_id in (select lcia_att_id  from links_cat_item_atts 
                                    where  lcia_link_id = LIMIT_ID 
                                     and  lcia_type = 'ITEM' );

-- you can go back and add FK constraints and other things if you choose.  

-- I use this model for  several things 
-- do not treat price as a attribute this will just muddy the waters 
-- if you do treat everything as attribute it means you have to track its data
--type in the database



Select * from item, atts, cats, links_cat_item_atts
    inner join links_cat_item_atts on item_id = lcia_link_id and lcia_type = 'ITEM'
    inner join cats on lcia_cat_id = cats_id 
    inner join atts on lcia_att_id = atts_id
    where item_price between 10 and 50
    and cats_name = ' hi there cats '
    and atts_value = 'filter on atts' ;
Related Question