Database design issue, with multiple variables and constraints

Question

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.

• Michael Kutz: What are your top queries?
• user3541631: @MichaelKutz - I added more details. my main queries are related to searching based on this attributes values.
• Chessbrain: Could you please provide the DDL for these tables? As it will make it easier for everyone to give you a concrete solution to your problem.

Answer 1

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' ;

• user3541631: price was just an example. I have many technical attributes, like length (between min and max) , modal phase (which is a list) and I want to search by them. If I add all of the values ones as columns will be many columns for product, with a lot of Nulls, because are specific to a category/subcategory etc.
• zsheep: another thing you can do is add a Type field to atts table, so you now how to cast the atts_value so searching and sort order coming back from the server makes sense. Nothing more annoying to have 100 come before 9 because its type is text. this makes the SQL queries very complex. I have special functions written to simply extracting data from the atts table so the data comes back as int, decimal, text, char() etc..

Answer 2

Your post actually has multiple questions.

• How do I model this?
• How do I enforce this?

By themselve, each one would be a very good question for this site. But, the complexity of the answers (taken as a whole) may generate a 3rd question.

• How do I design a program for this?

How do I model this?

There are 4 different ways to model "related widgets"

1. broad table (1 table with LOTS of columns)
2. in a collection (1 table with a column that holds the XML or JSON version of the information)
3. multiple tables (1 table each)
4. multiple tables+ (1 table each that reference a common Parent Table so that the PK/Sequence applies to all widgets)

Method (4) requires you to know how to model (and enforce) a 1:1 relationship. This is because you need to ensure that: foreach row in the parent, there must be (at least) 1 row in the (appropriate) child.

How do I design a program for this?

This model is going to get complicated very fast.

I highly recommend that the application(s) interact with the database by following the MVC concept.

• Model - this section covers your tables, etc. They are (mostly) hidden from your application(s).
• View - this is a normal DB VIEW. Sometimes, you can use the actual table directly. you will probably need>1 VIEW
• Control - "Control" of the data is done through APIs. These are the custom functions/procedures that you create within the database. They do all the hard work.

With some data models, you can create an INSTEAD OF trigger on the VIEW. However, I recommend that this trigger does the actual work by calling your APIs.

How do I enforce this?

The enforcement of a multi-row/multi-table constraint is done via CREATE ASSERTION. Sadly, none of the major RDBMSs support ASSERTION at this time.

As a workaround, you need to implement the ASSERTION via DIY (Do It Yourself). That is: build the ASSERTION into your APIs.

NOTE From my experience ASSERTIONS don't work well, if at all, when they are implemented within a trigger.

This is because of the required locking.

Example of required locks

• when you validate the value of PRICE for an Item, you first need to aquire a lock for the definition of PRICE.
• when you modify the definition of PRICE, you first need to aquire a lock on all affected Items (or, the entire ITEMS table)

Appropriate locking is also needed:

• when you modify List values for an Item
• when you modify the Catagories that an Item belongs to
• when the definition of a List Attribute is modified
• when the Catagory-Attribute relationship is modified (definition of a catagory)