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?

CodePudding user response：

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.

CodePudding user response：

Also, there can be duplications - two or more same properties for a single product.

Use UNIQUE to prevent from duplications

w3schools.com - UNIQUE