0

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?

5
  • Please put all & only what is needed to ask your question in your post & relate it specifically to your question. Please use text for what can be given via text. Why are images of text, code and mathematical expressions discouraged? (Also: Why should I not upload images of code/data/errors when asking a question?)
    – philipxy
    Mar 27, 2023 at 12:54
  • There are many information modelling & diagramming & DB design methods. What is your reference for yours? Where & why are you 1st stuck following it, after having done what? PS Rearrangements for subtyping/inheritance is not DB normalization or denormalization. The former involves outer joins & the latter 2 involve natural join. Why do you think this has to do with normalization? PS FKs are wrongly but ubiquitously called "relationships", and "1:M" etc are cardinalities of relationships represented by projections of tables (sometimes containing FKs). PS minimal reproducible example
    – philipxy
    Mar 27, 2023 at 13:06
  • @philipxy, I apologize if I chose wrong format to support my question, I deemed a github gist with interactive mermaid graph a viable option but now I see that MRE would have been a better choice apparently. As for your second comment, after reading through that I think I should take on a more systematic approach to what I am researching which I luckily can achieve just by answering questions you proposed to myself, thank you for that.
    – papshmeare
    Mar 27, 2023 at 13:37
  • Posts need to be self-contained. Also text should be used for what can be given via text. Moreover mermaid has a standard text format. (But you should give a legend for it, as you should also for an image.) It's not clear what you are trying to say or what is a problem. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. (What does "two one to zero relationships visually" mean? What's your reasoning that it's "possible"? Are you addressing a product being of neither or of more than 1 subtype?) Best to say how you are stuck following your method.
    – philipxy
    Mar 27, 2023 at 13:51
  • EAV is an antipattern for DB subtypes per se, it is for avoiding implementing user requests via DDL when that is merited. How can you represent inheritance in a database? More How to design a product table for many kinds of product where each product has many parameters More And more Re EAV And subtyping<>normalizing.
    – philipxy
    Mar 27, 2023 at 14:06

1 Answer 1

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.

1
  • thank you for your answer! EAV data model, in fact, solves my root problem completely even though I phrased the question wrong. I was actually concerned about precisely that kind of scenario where you have potentially vast amount of attributes for a given entity.
    – papshmeare
    Mar 27, 2023 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.