0

Hi am making a simple Website with commenting System. User can login with social network ID (FB, Twitter et al) or register with the site and comment (there are user who will not wat to use their FB accounts for this). I have the two tables available (one for HybridAuth and another for local user table). Now my problem is how do I design the table and whole system to function that way? Basically my problem can be broken as follows: Comments table need ID of who is commenting (FK from either of the two but not both).

So what is the common approach (since many sites use disqus, FB, twitter et al for that) used?

If there is any link here at SO or elsewhere I will be glad to get pointed to.

Stefano Mtangoo
  • 6,017
  • 6
  • 47
  • 93

1 Answers1

1

There are two approaches:

#1 Exclusive FKs

Have both FKs and a CHECK constraint on top of them, that makes sure one of them is NULL, as mentioned here.

Note that some DBMSes (most notably MySQL) don't enforce CHECK constraints, in which case you'll need to do that from triggers or client code.

#2 Inheritance

"Inherit" both kinds of users from a common table, then reference just the common table. Here is an example of how that can be done for "commentable" and "taggable" and "likable" content, similar principle can be applied to users.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • how do you do that in triggers? In Client code it can be done but I see it will be a bit expensive (check first if Null use second) – Stefano Mtangoo Dec 03 '13 at 06:48
  • while am waiting for your comment I will accept the answer since it gives me right direction! – Stefano Mtangoo Dec 03 '13 at 06:51
  • @Stefano I'm not sure I understand your question. You'd simply create an INSERT/UPDATE trigger that checks that explicitly and throws an exception if both FKs are non-NULL. The exact syntax/details vary from DBMS to DBMS. – Branko Dimitrijevic Dec 03 '13 at 11:47
  • I have never done triggers in any DBMS (never met anything needing them). I use MySQL with Yii – Stefano Mtangoo Dec 03 '13 at 20:38