0

I'm designing a database table called answers. The fields would be source_type and source_id, etc. The source_type is "question" or "comment", which are tables. Now the problem is I want source_id in answers table to reference either id in questions or id in comments depending on the value of the source_type field.

So in normal situation it would be either this:

$table->foreign('source_id')->references('id')->on('questions');

or this

$table->foreign('source_id')->references('id')->on('comments');

However how can I'm not sure how to add restriction in either table?

Margo Eastham
  • 605
  • 1
  • 10
  • 20
  • You cannot model a polymorphic relation using sql constraints. A foreign key can only refer to one table. – Needpoule Apr 21 '15 at 07:31
  • @MrShibby Thank you, then what would be a good database design for this particular situation? – Margo Eastham Apr 21 '15 at 07:39
  • 1
    Note that laravel and eloquent don't need foreign key to create relations between your model. But if you want to keep a good database design, i found two interesting answers: http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables and http://stackoverflow.com/questions/922184/why-can-you-not-have-a-foreign-key-in-a-polymorphic-association – Needpoule Apr 21 '15 at 07:46

1 Answers1

1

One of the options in this situation is to define 2 fields question_id and comment_id . Both of these are nullable foreign keys to respective table.

If one of these is set then you know which type it is.

You can also not define the foreign key to the source_id but then there is higher risk of data corruption and you cannot define proper Eloquent relationship for it.

Margus Pala
  • 8,433
  • 8
  • 42
  • 52