EAV model - How to restrict product properties?

Question

I have the following structure of my database, implementing a simple EAV model (see pic):

My product has a type, which through the junction table restricts prop_names, available for this product. And here everything is clear. BUT: Then I've added a prop_values table to keep the properties values for each product. It has reference to products through prod_sku and to prop_names through prop_id. And here the problem comes: One can add to any product any properties - even those, which are not allowed for this product type. Also, there can be duplications - two or more same properties for a single product.

Is there any way to restrict this on the database level?

After the @BillKarvin's answer, I've tried the below CREATE code, but failed with the 'Foreign key constraint is incorrectly formed' error when creating the last table (property_values).

I have found my error - I forgot to add a KEY to the products table. Below is the corrected (working) version of my code:

CREATE TABLE product_types (
  id INT PRIMARY KEY,
  product_type varchar(50) NOT NULL,
  block_css_id varchar(50) NOT NULL,
  block_description varchar(50) NOT NULL
);

CREATE TABLE products (
  sku varchar(50) PRIMARY KEY,
  name varchar(50) NOT NULL,
  price decimal(20,2) unsigned NOT NULL,
  id_product_type INT NOT NULL,
  FOREIGN KEY (id_product_type) REFERENCES product_types (id),
  KEY (sku, id_product_type)
);

CREATE TABLE property_names (
  id INT PRIMARY KEY,
  property_name varchar(50) NOT NULL,
  property_css_id varchar(50) NOT NULL,
  property_input_name varchar(50) NOT NULL
);

CREATE TABLE junction_ptype_propname (
  id_productt_type INT NOT NULL,
  id_property_name INT NOT NULL,
  PRIMARY KEY (id_productt_type, id_property_name),
  FOREIGN KEY (id_productt_type) REFERENCES product_types (id),
  FOREIGN KEY (id_property_name) REFERENCES property_names (id)
);

CREATE TABLE property_values (
  id INT NOT NULL PRIMARY KEY,
  product_sku varchar(50) NOT NULL,
  property_id INT NOT NULL,
  property_value decimal(20,2) NOT NULL DEFAULT 0.00,
  id_prod_type INT NOT NULL,
  UNIQUE KEY (product_sku, property_id),
  FOREIGN KEY (product_sku, id_prod_type) REFERENCES products (sku, id_product_type),
  FOREIGN KEY (property_id, id_prod_type) REFERENCES junction_ptype_propname (id_property_name, id_productt_type)
);

Answer 1

I would design this in the following way:

There are few important differences from your model:

• prop_values has a unique key on (prod_sku, prop_id) so you can only have one instance of a given property per product sku.

• prop_values has a prod_type column, and this references products, using both columns (sku, prod_type).

• prop_values has a compound foreign key to junction_ptype_propname instead of prop_name.

Now the prod_type in prop_values can have a single value per row, and it must reference the correct product type in both the products table and the junction_ptype_propname table. So it is constrained to be a valid property for the given product, and a valid property for the product type. You therefore cannot add a property to a product that isn't legitimate for that product's type.

Here's the DDL:

create table prod_types (
  id int primary key,
  type_name varchar(30) not null
);

create table products (
  sku varchar(30) primary key,
  name varchar(30) not null,
  type int not null,
  foreign key (type) references prod_types(id),
  key(sku, type)
);

create table prop_names (
  id int primary key,
  prop_name varchar(30) not null
);

create table junction_ptype_propname (
  id_prop_name int not null,
  id_prod_type int not null,
  primary key (id_prop_name, id_prod_type),
  foreign key (id_prod_type) references prod_types(id),
  foreign key (id_prop_name) references prop_names(id)
);

create table prop_values (
  id int primary key,
  prod_sku varchar(30) not null,
  prod_type int not null,
  prop_id int not null,
  prop_value decimal not null,
  unique key (prod_sku, prop_id),
  foreign key (prod_sku, prod_type) references products(sku, type),
  foreign key (prop_id, prod_type) references junction_ptype_propname(id_prop_name, id_prod_type)
);

This question is fun because it's a case of using Fifth Normal Form. Many articles on database design claim that normal forms past the Third Normal Form aren't used. But your model disproves that.