本文介绍了SQL Server表设计与多对多关系的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个包含以下表格的数据库架构:

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表设计与多对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-17 06:54
查看更多