Alex Rivera | Logout

How to model a database with many m:n relations on a table

Asked 2011-08-16T19:13:57.570
11

I am currently setting up a database which has a large number of many-to-many relations. Every relationship was modeled via a link table. Example:

A person has a number of jobs, jobs are fulfilled by a number of persons. A person has a number of houses, houses are occupied by a number of persons. A person has a number of restaurants he likes, restaurants have a number of persons who like the restaurant.

First I designed this as follows:

Tables: Person, Job, House, Restaurant, Person_Job, Person_House, Person_Restaurant.

Relationships 1 - n: Person -> Person_Job, Person -> Person_House, Person -> Person_Restaurant, Job -> Person_Job, House -> Person_House, Restaurant -> Person_Restaurant.

This leads pretty quickly to a crowded and complex ER model.

Trying to simplify this I modeled it as follows:

Tabels: Person, Job, House, Restaurant, Person_Attributes

Relationships 1 - n: Person -> Person_Attributes, Job -> Person_Attributes, House -> Person_Attributes, Restaurant -> Person_Attributes

The Person_Attributes table should look something like this: personId jobId houseId restaurantId

If a person - job relationship exists, I'll add an entry looking like:

P1, J1, NULL, NULL

If a person - house relationship exists, I'll add an entry looking like:

P1, NULL, H1, NULL

So the attributes table in the second example will have the same number of entries as the link tables of the first examples added up.

This simplyfies the ER Model a lot, and as long as I build indexes for personId + jobId, personId + houseId and personId + restaurantId, there won't be a lot of performance impact, I think.

My questions are: Is the second method a correct way of modelling this? If not, why? Am I right about performance impact? If not, why?

MySQL Workbench example of what I mean can be found here:

database-design relational-database entity-relationship database-schema

Edit
Report

2 Answers

21

Your design violates Fourth Normal Form. You're trying to store multiple "facts" in one table, and it leads to anomalies.

The Person_Attributes table should look something like this: personId jobId houseId restaurantId

So if I associate with one job, one house, but two restaurants, do I store the following?

personId jobId houseId restaurantId
    1234    42      87         5678
    1234    42      87         9876

And if I add a third restaurant, I copy the other columns?

personId jobId houseId restaurantId
    1234   123      87         5678
    1234   123      87         9876
    1234    42      87        13579

Done! Oh, wait, what happened there? I changed jobs at the same time as adding the new restaurant. Now I'm incorrectly associated with two jobs, but there's no way to distinguish between that and correctly being associated with two jobs.

Also, even if it is correct to be associated with two jobs, shouldn't the data look like this?

personId jobId houseId restaurantId
    1234   123      87         5678
    1234   123      87         9876
    1234   123      87        13579 
    1234    42      87         5678
    1234    42      87         9876
    1234    42      87        13579

It starts looking like a Cartesian product of all distinct values of jobId, houseId, and restaurantId. In fact, it is -- because this table is trying to store multiple independent facts.

Correct relational design requires a separate intersection table for each many-to-many relationship. Sorry, you have not found a shortcut.

(Many articles about normalization say the higher normal forms past 3NF are esoteric, and one never has to worr

answered 2011-08-16T20:29:10.837
1

In my humble opinion I would go for the first model. It's probably a more complex model but in the end it will make things easier when you're extracting info from tables and the application code could get dirtier or more unreadable for other programmers. Beside, there are some authors that wouldn't reccommend to use multipurpose tables like that.

In the end you must go with whatever suits you better. We don't know the whole context so can't help you too much to decide. But, for what you're saying and I'd definitely go for option number one.

answered 2011-08-16T19:33:00.417

Your Answer