1

We have three different types (user, article, comment) a user can subscribe to. Each type has a separate table. I want the user to be able to subscribe to another user, article or comment.

Subscription table columns: 

id(integer, AI)
type('String, can be 'article', 'comment', 'user')
ref('integer', indicates the index key to an external table)
user_id('integer', foreign key to the users table)

So this row would be a user with id=101 who is subscribed to a comment with id 99

1 | comment | 99 | 101

user with id=101 is also subscribed to article with id=88

1 | article | 88 | 101

You get the idea. Is this a good design?

  • 2
    This is a pretty common design. The main misfeature is that you can't implement a foreign key constraint on `ref`, because the table it refers to varies. – Barmar Dec 24 '13 at 10:51

2 Answers2

0

I would avoid this approach because of the 'magic string' element. Your queries will have to rely on reading a string to decide whether the user is subscribed to something.

I would consider taking a different approach and having a table that just represents a User, then having 'link' tables to match users (by their ID) with a suscriptions of a particular type.

For example:

 Users                UsersInArticles              Articles
 -----------          ----------------             ----------
 UserID               UserID                       ArticleID
 Name                 ArticleID                    Title
 Address                                           Body
 etc...                                            etc..

The UsersInArticles table represents a user (by UserID) that is subscribed to a particular Article (by ArticleID).

This approach allows you to maintain relational integrity with foreign keys relationships (you can ensure that a user cannot subscribe to an article that does not exist in the database for example).

Also, there are no magic strings involved.

With this approach, to find all users subscribed to an article (e.g. article 123), you can just do the following query (rather than comparing against a string column to find 'Article' etc).

SELECT UserID from UsersInArticles WHERE ArticleID = 123

I also suspect that this shema would allow faster queries than having a varchar column representing the subscription type.

Dave S
  • 1,403
  • 1
  • 15
  • 23
0

Is this a good design?

No.

The DBMS cannot enforce the referential integrity for you (via foreign keys). And in a concurrent environment, implementing referential integrity manually is surprisingly difficult to do correctly (without race conditions) and with good performance.

Instead, you can just use multiple Subscription tables (one for each type of "subscribable" item). Or if you wish to avoid table proliferation, use multiple FKs or inheritance, similar to this (but you don't necessarily need to enforce FK exclusivity).

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167