很多很多表设计问题 [英] Many to many table design question

问题描述

最初我在我的DB，[Property]和[Employee]中有两个表。

每个员工可以有一个Home Property HomePropertyID FK字段到属性。

后来我需要建模的情况，尽管只有一个家庭财产，员工工作或覆盖多个属性。 p>

所以我创建了一个具有EmployeeID和PropertyID FK字段的[Employee2Property]表来建模这个多对多关系。

现在我发现我需要在员工和属性之间创建其他多对多关系。例如，如果有多个雇员是一个财产的经理或多个雇员在财产等进行维护工作。

我的问题是：

1. 我应该为每种情况创建单独的多对多表，或者应该创建一个更多的表，如[PropertyAssociatonType]，列出关联类型一个员工可以拥有一个属性，只需添加一个FK字段到[Employee2Property]，如PropertyAssociationTypeID，解释什么是关联？


2. 我很蠢，而且这一切都错了吗？

$感谢您的任何建议：）

解决方案

这是一个非常有效的问题。答案是：

以下内容建议使用单一的'typed'M：N关系：

• 您通常希望处理所有员工 - 属性关系，而与类型无关。


• 关联数目一直在变化，


• 有时，员工财产关系会改变其类型。

对，你最好是使用单独的关系。

Originally I had two tables in my DB, [Property] and [Employee].

Each employee can have one "Home Property" so the employee table has a HomePropertyID FK field to Property.

Later I needed to model the situation where despite having only one "Home Property" the employee did work at or cover for multiple properties.

So I created an [Employee2Property] table that has EmployeeID and PropertyID FK fields to model this many-to-many relationship.

Now I find that I need to create other many-to-many relationships between employees and properties. For example if there are multiple employees that are managers for a property or multiple employees that perform maintenance work at a property, etc.

My questions are:

1. Should I create separate many-to-many tables for each of these situations or should I just create one more table like [PropertyAssociatonType] that lists the types of associations an employee can have with a property and just add a FK field to [Employee2Property] such as PropertyAssociationTypeID that explains what the association is? I'm curious about the pros/cons or if there's another better way.
2. Am I stupid and going about this all wrong?

Thanks for any suggestions :)

解决方案

This is a very valid question. And the answer is: it depends

The following things suggest using a single 'typed' M:N relationship:

• you often want to process all employee-property relationships, independent of type
• the number of associations is changing all the time, i.e. new types get invented.
• a employee property relationship sometimes changes its type.

If these statements are more wrong then right, you might better be of using separate relationships.

这篇关于很多很多表设计问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！