问题描述
我有一个包含以下表格的数据库架构:
I have a database schema that includes the following tables:
- 人们
- 组织
- RelationshipTypes
我要设计的是关系类型的概念,然后是该关系的结构.因此,一个人可能与另一个人或组织有某种关系.一个组织可以与另一个组织或一个人有关系.
What I want to design is a concept of a relationship type, and then the structure of that relationship. So a person could have a type of relationship with another person or with an organisation. An organisation can have a relationship with another organisation or a person.
我有以下模式,但是我想将其运行在社区之外,以查看是否有更好的主意.
I've got the following schema, but I'd like to run it past the community to see if there are any better ideas.
CREATE TABLE OrganisationRelationshipTypes
(
ID INT PRIMARY KEY IDENTITY,
RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
FromOrganisationID INT NOT NULL REFERENCES Organisations(ID),
ToOrganisationID INT NOT NULL REFERENCES Organisations(ID)
)
CREATE TABLE PersonRelationshipTypes
(
ID INT PRIMARY KEY IDENTITY,
RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID),
FromPersonID INT NOT NULL REFERENCES People(ID),
ToPersonID INT NOT NULL REFERENCES People(ID)
)
CREATE TABLE OrganisationPersonRelationshipTypes
(
ID INT PRIMARY KEY IDENTITY,
RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
FromOrganisationID INT NOT NULL REFERENCES Organisations(ID),
ToPersonID INT NOT NULL REFERENCES People(ID)
)
CREATE TABLE PersonOrganisationRelationshipTypes
(
ID INT PRIMARY KEY IDENTITY,
RelationshipTypeID INT NOT NULL REFERENCES RelationshipTypes(ID)
FromPersonID INT NOT NULL REFERENCES People(ID),
ToOrganisationID INT NOT NULL REFERENCES Organisations(ID)
)
其想法是它涵盖了以下情形:
The idea of this is that it covers such scenarios:
- 一个组织与另一个组织有业务往来
- 一个人是另一个人的父亲
- 一个组织最近雇用了这个人
- 最近在该组织工作的人
这种模式似乎有些混乱,但是目前我想不出另一种选择.
This schema seems a little messy, but at the minute I can't think of an alternative.
您有什么建议吗?
推荐答案
Party
通常用作个人或组织的通用术语,例如合同中的缔约方" ...
Party
is often used as a generic term for person or organization, as in "parties in contract" ...
这篇关于SQL Server表设计与多对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!