Edit Question - KnowledgeHub

Title Body

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: <a href="http://www.2shared.com/file/3GBnodEZ/example

Tags (comma-separated) Cancel