Relational tables with same content, custom solution

application-designdatabase-designdesign-pattern

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.

Best Answer

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.

Related Solutions

Does ‘Cities’ deserve a separate table

The design doesn't meet third normal form, but not just because of the city. The fields STREET, CITY are functionally dependant on each other (if you change the city, the street should probably change as well and vice-versa). You could also have the same street, city combination represented in different ways (Foo St, Foo; Foo Street, Foo; etc.).

To normalise this you would create a new table ADDRESSES which has the street, city etc. in and link the customer to that via an address id. This would also allow you to list several addresses for a customer (via a link table) if this is what you require.

This still leaves you to decide whether to extract the city into it's own table. To fully meet 3NF you should create a cities table, whether you need or want to depends on the answer to the following questions:

Do you (or will you) have additional attributes of the city (e.g. population, county/state, display name etc.)
Are you executing a query to generate a list of distinct cities to pick from (your comment suggests yes)

If the first point is true, then you should definitely create a CITIES table, otherwise you could end up with one city having different populations etc. If the second is true then it's a very good idea to have a separate table as the query to list all your cities will scale much better - you'll only have to scan this table rather than the (almost certainly larger) CUSTOMERS table and then get the distinct cities from that.

How to model the Twitter lists functionality

I'll try to explain why they merge all relationships into one table.

Whether a users follows, likes or comments a photo all these activities are somewhat similar they involve a user and photo. Note that the comment also requires some text.

So instead of having 3 tables like your data model they went with one.

I don't like that approach though because it merges data that may is similar in its appearance but differs in meaning. Also the comments require extra data (content) that is empty for likes and follows. Having the type described as a string as in their example is also bad (better make an extra table).

A database model would like this for your problem

User(**UserID**, Name, ....)
Post(**PostID**, UserID[FK]Data, Timestamp, ...)
Follow_Users(**UserID[FK]**, **FollowingUserID[FK]**)
Follow_Posts(**UserID[FK]**, **PostID[FK]**)

However this is a database model and not a Data model. A Data model that is kept in memory within some application will be different because it features pointers to other objects and arrays...

Related Question