Relational tables with same content, custom solution

Question

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this:

Table foo and table foo_comments, where foo_comments contains id, foo_id and other columns.

Right now this is the only way that I am using comments in my app. I did get a note from somebody that, if they will update the app in the future and they will want to add comments also for BAR, I should add something custom from right now, even if I don`t need it right now. So I was advised to add something like this

A table comments (for general use), that would contain a type column (where type can be an int representing the table which is referring to: foo, bar or something else) and a type_id column that would contain the id of the row from the main table.

So it`s this a good practice? Should I build something like this, just because the app can be changed in the future? Or I just keep it with foo_comments table, and then if the app will add comments for BAR table, I would create a bar_comments table also.

Answer 1

I would keep them separate. Although the details differ, your circumstance smells a lot like the One True Lookup Table antipattern. By combining you will have problems defining foreign keys (which parent to point at?), amongst other things. Indexing strategy will essentially be about spliting the combined table into its constituent parts. And so on...

The justification for combining would be if Foo and Bar are sub-types of some more abstract entity type and all comments could hang off this super-type.

Answer 2

You have a lot of factors to weigh, but here's where I would start:

1. Will the columns for FOO comments and BAR comments be exactly the same?

2. Would you ever need to group all comments on both foos & bars, treated identically (e.g. would you ever need a database query to sort/filter/count both foo & bar comments by date/time)?

3. How likely is BAR to need comments, and how likely are other 'models' (I'm going to start using the MVC term 'models' to represent FOO/BAR/ETC) beyond BAR to need comments, and how many models could possibly need comments?

The two main approaches are those which you mentioned: create a comments table with a 'type' field, or use separate tables for each comment type, as needed.

So, start with question #1:

If the comment tables are likely to require different fields, you're going to potentially run into problems trying to "share" the same comments table between foo and bar, depending on the app's requirements and software. In that case, you're more likely to be better off keeping them separate. With a unified table, you can always add fields for BAR comments that are unused by FOO comments, but it can result in extra headaches in the app development if the comment tables can't be identical.

If the tables are identical, I would consider #2:

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.

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).

Now --

If you are going to go with a fully unified table, because of any of your requirements:

In your unified comments table, add your comment_type_id column. If possible, I would strongly consider creating an index for that column for faster filtering and sorting. You can make it an integer as you suggested.

In your app you can now relate any model to it's own specific rows in the comments table, just by assigning it a comment_type_id.

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.

Adding additional comment types can be managed by adding the comment_type_id property to any model that needs it's own comments, and then making sure that model shares/inherits the same code for managing its related comments.

I might recommend creating a table to manage the comment types. A table like this can be useful for bookkeeping and can be used to look up the comment type names from within the code, but is totally optional.

A comment_types table could just be id (auto increment) and name (as a label) columns. To start you'd have something like:

id | name 1 | foo 2 | bar

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.

Answer 3

If you need to add comments to other entities, data normalization is your friend.

First, your idea of creating a general purpose comments table is a good first step. The problem with a "comment type" column is that you don't gain any referential integrity with this data structure ensuring that a certain type is only related to one certain entity. Instead, you need an intermediate table.

Let's say you have a table called blog_posts:

blog_posts
=============================
blog_post_id (PK)
-----------------------------
title
body
=============================

You would want your comments table:

comments
=============================
comment_id (PK)
-----------------------------
comment_text
(other columns)
=============================

And finally a join table to associate blog posts with comments:

blog_post_comments
=============================
blog_post_comment_id (PK)
-----------------------------
blog_post_id (FK, blog_posts)
comment_id (FK, comments)
=============================

Now adding comments to another entity just requires an additional join table.

Let's say you want to add comments to images or photos:

images
=============================
image_id (PK)
-----------------------------
(other columns)
=============================

Now the join table:

image_comments
=============================
image_comment_id (PK)
-----------------------------
image_id (FK, blog_posts)
comment_id (FK, comments)
=============================

Yes, you are building another table for each new entity that needs comments, but you gain referential integrity so that you can't mistakenly show blog posts comments on an image, and vice-versa.