How will you handle many-to-many relation in one table with multiple entity types?

General Miscellaneous
1

Hey.

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):

First:

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’).

Second:

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?

Thanks.

2

Is there a reason you are putting all those different relations into one table?

Why not break it up into person_university, person_job and person_project relationship tables? Unless you’re dynamically creating a bunch of different types of relationships, I would do it this way.

3

Thanks for the reply.

The first reason why I’m trying to keep everything in one table, is because I don’t know at the moment how many related item types there might be in the future. And adding new field, or new item type is easier and faster than adding new table.

Another reason, is that with one table I have a unified approach to access all related items from one model. If I’ll have several tables, it’ll be more complex approach, though I’ll still be able to set all those relations in the person model.

I was thinking about this method earlier, but I dropped it. Maybe I should think about this approach again.

Anyway, still searching for the best solution…