CODEWITHSUNDEEP
database-design
DandyCC
DandyCC

Reputation: 361

DB design. Inheritance

Imagine that you are developing a web app in which different “entities” (with different attributes) send messages and interact with public messages from other entities. These entities might be of several types: just users, a private company, an academy…

To design the DB I can think of two options that I think would work but I don’t know which one is better or if there is any other better option that didn’t occur to me.

Option 1: Separate different entities in different tables. In this case, the table that stores messages should have at least two columns to identify the entity that published it, id and type.

Option 2: Make all entities inherit from a parent class. Thus, the table that stores messages would require only a foreign key that point to the id of the entity that published it. This option seems much better, but the problem is that I can’t find common attributes to all entities, so the parent table would only have one, the id.

Which option do you think is better? Is there any better alternative?

PS: For option 2, would it be necessary that the child tables have their own id or defining the foreign key as primary key would be enough?

Thanks.

Upvotes: 1

Views: 168

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I hope I have understood your question correctly.
We have several entities, they publish messages. We are trying to find optimal design solution to store messages.

Option 1: Separate different entities in different tables. In this case, the table that stores messages should have at least two columns to identify the entity that published it, id and type

Having at least two columns (id , type) to identify the publisher is not a good design. Since we are trying to replace foreign key of publisher with custom made solution, The side effect will be loosing data integrity. Think about what will prevent a message being saved with id of none-existing publisher! or when some new entity type is going to be defined.
The correct way will be having publishers foreign key and that will end up with multiple null-able foreign key columns inside message table. That is not appreciated yet better solution of id+type.

Option 2: Make all entities inherit from a parent class. Thus, the table that stores messages would require only a foreign key that point to the id of the entity that published it. This option seems much better, but the problem is that I can’t find common attributes to all entities, so the parent table would only have one, the id.

I will choose this approach.

I can’t find common attributes to all entities

The nature of message-publishing is the most obvious shared functionality(attribute). Yet i think there are other shared attributes like name, registration date, status ...

For option 2, would it be necessary that the child tables have their own id or defining the foreign key as primary key would be enough

Modeling inheritance in RDBMS has one solution:
Parents primary key will be a foreign key inside child table and this foreign key must be the primary key of child table.

Upvotes: 1

Odonno
Odonno

Reputation: 419

With the arrival of NoSQL, these kind of conception questions is now easy to solve. Imagining you have a web, like you said, where you can save any kind of data (a web message that is related to one person, a group of person or a sole organisation), there is plainty of solution now.

I will give one major example of how document NoSQL database fix that problem. You can solve all of your messages in a single collection/table. Here is an example :

[
    {
        "message": "Test message",
        "destination": [
            { "type": "user", "username": "user1" }
        ]
    },
    {
        "message": "Test message",
        "destination": [
            { "type": "org", "username": "company1" },
            { "type": "user", "username": "user3" },
        ]
    },
]

Of course, NoSQL databases play with ids too, like relational databases. In the previous example, you could put an id on each message and inside each object of destination list.

Upvotes: 0

Related Questions