Correct entity relationship diagram representation of a normalized table with a NOT NULL foreign key

Question

Intro

Hi everyone, this is mainly a classification/theoretical question on the topic of inheritance and normalization techniques in database design and their appropriate representation in entity relational diagrams. In a practical implementation it doesn't present a challenge as you could just set the attribute in question as IS NOT NULL and be done. However for a graphical representation I am a little confused on how to do it correctly. Here is a github gist link with a interactive mermaid diagram representing a hypothetical scenario on a particular example: gist

Problem

The important part is in this section: Assume we have an entity called PRODUCT which might have different types of attributes depending on the product in question(eg. Physical product/Digital product). For that reason we introduce two "subtables" whose PKs refer to a PRODUCTs FK attribute product_type_id. It is clear that a PRODUCT can only have a singular product_type_id but because it can be either PhysicalProduct or DigitalProduct what kind of relationship do these two "subtables" have with PRODUCT? So far I deduced that it must be one to (zero or one) as presented in the graph. That's where the problem(perhaps non-existent) lies. If we have two one to (zero or one) relationships to a mandatory IS NOT NULL attribute doesn't it infer a possibility of two one to zero relationships visually or is it something not clicking in my head here and that is how it is supposed to be in this kind of scenario?

Answer 1

The problem here is multiple tables which do the same thing. In you schema, both PhysicalProduct and DigitalProduct describe a product. That is a no-no for normalization.

And just think about the product. What are you selling, that can be can be either Physical or Digital. Why "either...or", why not both? For example, I can have hardcover book as well as e-book. Or mp3 and vinyl of the same song... I cannot have a digital grocery, but it would still be weird if you start describing potatoes and strawberries by two different tables (vegetables/berries).

The situations like this, are usually solved by EAV data model. The PRODUCT table is a "general" description of the product, and we have just one PRODUCT_ATTRIBUTE table, which would point to the PRODUCT as many-to-one.

create table PRODUCT (
   id integer primary key,
);
create table PRODUCT_ATTRIBUTE (
   product_id integer not null foreign key references PRODUCT(id),
   attribute_name text not null,
   attribute_value variant not null,
   primary key (product_id, attribute_name)
);

All the fields you put into PhysicalProduct and DigitalProduct ("weight", "dimensions", "file_size", etc) become attributes in the PRODUCT_ATTRIBUTE table. If your database does not support variant datatypes, you can make several fields like "value_int", "value_string", or just one text field with a convertion on a client when necessary.