DB schema help for Product Attributes (Filtering)

Hi!

I need help figuring out the best database schema for a Product Attribute system. It's a pretty specific system I'm trying to build. It should function a lot like the one on PriceSpy. See here: https://pricespy.co.uk/category.php?k=103

• Each "Attribute" may or may not have fixed values. For example. "Operating system" have fixed values of "Android, iOS, etc". But "Manufacturer website" is a product specific value.

• Each "Attribute" and "Attribute Value" can be of different type. For example "boolean, string, integer, timestamp, etc".

• Each "Attribute" may or may not belong to an Attribute Category (Camera, Dimensions, Functions, etc).

What I have done so far looks like this:

AttributeCategory [attribute_categories]

id

index

name [translatable]

Attribute [attributes]

id

index

attribute_category_id

parent_id

name [translatable]

fixed_values [true, false]

type [boolean, text, integer, decimal, timestamp]

unit

description (has many AttributeValue)

AttributeValue [attribute_values]

id

index

attribute_id (belongs to Attribute)

type [boolean, text, integer, decimal, timestamp]

value_boolean

value_text

value_integer

value_decimal

value_timestamp

Pivot [attribute_value_product]

attribute_value_id

product_id

Pivot [attribute_product]

attribute_id

product_id

type [boolean, text, integer, decimal, timestamp]

value_boolean

value_text

value_integer

value_decimal

value_timestamp

Pivot [attribute_product_category]

index

attribute_id

product_category_id

(This one is to attach Attributes to ProductCategories, for the Product Category filter page that I'm linking to above)

So the most important tables here are the two Pivots, attribute_value_product and attribute_product.

If a product has a fixed value Attribute, like Operating system, it goes in the atribute_value_product pivot table.

If a product has a NON-fixed value Attribute, like Manufacturer website, it goes in the attribute_product pivot with those extra columns to store the actual value.

All this SEEMS to work, but I can't stop wondering if there is just a simpler way to achieve this, or at least through better naming of the tables and models. Maybe you pros can help out.