We have 2 entities to represent in the database:
- Entity A having attributes (x, y, z, r).
- Entity B having attributes (x, y, z, s).
The 2 entities have 3 identical attributes, and only 1 different attribute. And although being very similar, they are not related and not intended to be used together in the business logic.
There are 2 approaches (maybe more) to represent these:
- Create two separate tables, one for each entity.
This is the straight forward method. It results in two relatively small tables, and cleaner queries and business logic. But the tables are almost identical, so it's clearly redundant in a way (Imagine the identical attributes are much more than 3). - Create one shared table with 5 attributes (x, y, z, r, s, type).
(type) indicates the type of the entity being represented in this row (either A or B). This is assuming that attributes (r) and (s) are not mandatory so we can't rely on them to determine the type of the entity.
For example, this is the approach used by Wordpress to represent posts and pages (and a few other entities). It results in one relatively large table containing a lot of null fields, and relatively messy queries and business logic to filter the rows and separate the entities logically. But we end up with only one unique table instead of two redundant ones.
My questions are:
1- What are other advantages and disadvantages of each approach?
2- What are use cases for both approaches?
3- If the entities increased in number, or their relationships increased in complexity, will one approach be clearly better then the other? Like if instead of 2 entities, there are 20. Or the entities has many-to-many relationships with other entities in the database.
Thank you!