Thoughts about product attributes for Interchange 6

gistfile1.txt

Table attributes:

attributes_id: AUTO
name: name / code of attribute
title: displayed title of attribute

Table attributes_values:

attributes_values_id: AUTO
attributes_id: FOREIGN KEY attributes::attributes_id
value: name / code of value
title: displayed title of value
priority: for sorting

Table product_attributes:

product_attributes_id: AUTO
sku: FOREIGN KEY products:sku
attributes_id: FOREIGN KEY attributes::attributes_id
canonical: boolean (if 1 can only used for variants)

Table product_attributes_values:

product_attributes_values_id: AUTO
attributes_values_id: FOREIGN KEY attributes_value::attributes_id
price_adjust: 

Examples:

attributes:

id = 1
name = size
title = size

attributes_values:

id = 1
attributes_id = 1
value = SIZE_0001
value_title = XS
priority = 10

id = 2
attributes_id = 1
value = SIZE_0002
value_title = S
priority = 9

id = 3
attributes_id = 1
value = SIZE_0002
value_title = XL
priority = 8

product_attributes:

sku = F001
attributes_id = 1

product_attributes_values:

id = 1
attributes_values_id = 1

id = 2
attributes_values_id = 2

So product F001 has only size XS and S.