Return to Answer

The danger in the unified table can be in linking from the comments back to either FOO or BAR. The code will have to check for the comment_type_id column of the comment and then query the appropriate FOO/BAR/ETC model's table for data. This is certainly feasible, and frameworks have methods to handle the relationships, but it is more work and setup on the software side.

Lastly, the more likely and further that I'm potentially going to need to expand comments to BAR and beyond to additional of models, I'm more likely to use a unified table, because it won't require me creating more and more identical tables, which can be tedious to maintain (adding a column to 12 different comment tables is more work than adding it to one).

A model's comment_type_id should then be used in queries to match only comments with the same value in their comment_type_id column, rather than directing the queries to an entirely different table. Each model that can have related comments (FOO/BAR/ETC) will have a different comment_type_id to be used when inserting into and querying the comments table, and you can share the code for the "Comment" model between them.

Finally, I would always consider #3. The more likely and further that I'm potentially going to need to expand comments to BAR and beyond to additional models, I'm more likely to use a unified table, because it won't require me creating more and more identical tables, which can be tedious to maintain (adding a column to 12 different comment tables is more work than adding it to one).

A model's comment_type_id should then be used in queries to match only comments with the same value in their comment_type_id column, rather than directing the queries to an entirely different table. Each model that can have related comments, as FOO/BAR/ETC will each have a different comment_type_id to be used when inserting into and querying the same comments table, and you can share the code for the "Comment" model between them. At the same time, if you want to grab all comments of all types, you only need to query one table.

If I need to be able to group/sort foo and bar comments in the same database queries (if I need them to be grouped together or counted as different types of the exact same thing), then you probably need to build the table as one. I'll discuss what I'd recommend for that at the end.

If I can assume the comments will never need to be queried as one entity (or that it's unlikely, or if they do, I can do it by querying each table separately and sorting with software), that would make me lean further toward separate tables.

Even when using separate (but identical) tables, the database queries to grab them can be built with almost identical code. By using either inheritance, a related model, or a shared behavior / helper class (concepts common to many software frameworks), you can create one class to run the same DB queries on each of the separate tables, by simply swapping the database table names that each instance or child of the parent class is configured to use. The table names can be a variable/property of the model in the code. In this way you get the benefits of separate tables without having to write or maintain entirely separate code.

If you need to be able to group/sort foo and bar comments in the same database queries (if you need them to be grouped together or counted as different types of the exact same thing), then you probably need to build one unified comments table. I'll discuss what I'd recommend for that at the end.

If I can assume the comments will never need to be queried as one entity (or that it's unlikely, or that if they do, I can do it by querying each table separately and sorting the results with code), that would make me lean further toward separate tables.

When using separate (but identical) tables, the database queries to grab them can be built with almost identical code. By using either inheritance, model relations, or a shared behavior / helper class (concepts common to many software frameworks), you can create one class to run the same DB queries on each of the separate tables, by simply swapping the database table names that each instance or child of the parent class is configured to use. The table name can be a variable/property of the model in the code. In this way you get the benefits of separate tables without having to write or maintain entirely separate code.

Lastly, the more likely and further that I'm potentially going to need to expand comments to BAR and beyond to additional of models, I'm more likely to use a unified table, because it won't require me creating more and more identical tables, which can be tedious to maintain (adding a column to 12 different comment tables is more work than adding it to one).