Assume, we have a table called person, and tables that might be related to the person: university, job, project.
So, each person can be attached to several unis, jobs and projects, and at the same time each uni/job/project can be attached to multiple persons. So this is a many-to-many relation.
How will you store this in the database in the way, that will be easier later to work with?
In the past when I had such relations, I was using one of two ways (somethimes 1st, sometimes 2nd):
Table person_relation has fields: person_id, university_id, job_id, project_id.
So each time when new relation needs to be added, I have to add new field to the table. But on the other hand, InnoDB handles all the foreign relations, and also it’s easy to get person joined with it’s related pins using Yii’s ->with(‘job’).
Table person_relation has fields: person_id, item_type, item_id.
So in this case new relation can be added easily by adding new item_type. But the problem here, that it doesn’t support native InnoDB foreign keys. And also you cann’t use Yii’s built-in feature ->with(‘job’). I had to write my own method that automatically joins related item, and also had to extend PopulateRecord method to assing those related items.
I hope with this simple example you got my idea. So, how would you handle this, maybe somebody knows a better way?