Trade-offs of using join table and type

Question

I am looking for an idea of ​​creating a database with a table that is linked to several other tables. A concrete example is a site with comments on various things: people can comment on articles, or they can comment on photos or comment on people.

There seem to be two ways to present this:

1) join tables for each other table TABLES:

• articles
• articles_comments
• comments
• comments_people
• comments_photos
• people
• Photo

or 2)

• articles
• comments
• people
• Photo

and the comment table will have a type field, and item_id will refer to another table.

The first approach seems more “correct”, and it seems that we should not have problems using foreign key constraints, while the second approach has fewer tables and can be “simpler” in some respects, but we can use the FK constraints, since item_id may be associated with multiple FKs (AFAIK - using mysql innodb). Perhaps it would be nice if in our application there were no two tables with several relationships (comments, photos, etc.) and 5-10 tables requiring relationships.

I am looking for advice on which is best suited.

Answer 1

The comment table with a type (enum btw) column looks like the best approach, because you might want to develop what “comment” is in the future.

Having four tables, as you described, also facilitates your Model class fields and their relationships.

Use "LEFT JOIN" in the comments table if this is a 0-to-many relationship. Otherwise, it is an "INNER JOIN".

Enjoy.

Answer 2

I personally do not like option number 2. This is a controversial topic, the concept of ONE BIG LOOKUP TABLE.

First, I agree with Yzmir-icky to manage. In addition, it is an insult to OO to create a relational database that looks a bit like mixing apples and oranges. Although for someone with an OO programming background (which includes ME ...), to think of a “COmment object” and its properties, think a little about database design. The table names in Example 1 clearly describe what happens in both the entities that they represent and the relationships between them (many-to-many lookup tables). They are also much better in their “normal” form.

RBDMS is a representation of objects and the relationships between them and is not necessarily always consistent with the principles of OO. In addition, the normalization rules are more favorable for option No. 1.

Option # 1, for me, is a more robust RDB design, and it’s easier to manage, more easily extensible and is likely to work faster.