I'm using PostgreSQL. I have the following entities Categories, Items, Attributes.
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
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:
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.