一个地址表可用于许多实体？ [英] One Address Table for Many entities?

问题描述

概念阶段的问题：

我有几个表（人，机构，工厂）每个都有许多地址（邮件，物理）

I have several Tables (Person, Institution, Factory) each has many kinds of Addresses (Mailing, Physical)

是否可以创建包含所有实体的所有地址的单个地址表？

Is there a way to create a single Address table that contains all the addresses of all the Entities?

我宁愿没有 PersonAddress 和 FactoryAddress 等一组表。

I'd rather not have a PersonAddress and FactoryAddress etc set of tables.

还有其他选择吗？

数据量最多只能是数千个地址，因此影响很小。

The amount of data will only be several thousand addresses at most, so light in impact.

推荐答案

我的提议基于以下原则：一个实体（个人，机构，工厂等）可以具有多个地址，通常是这种情况（房屋，企业等），并且可以共享一个地址按不同性质的实体：

My proposal relies on the principle that one entity (person, Institution, Factory, etc) can have multiple adresses, which is usually the case (home, business, etc), and that one adress can be shared by entities of different nature:

CREATE TABLE ADDRESS
(
    ID INT IDENTITY PRIMARY KEY NOT NULL, 
    .... (your adress fields here)
    id_Person ... NULL,
    id_Institution ... NULL, 
    id_Factory ... NULL 
)

主要限制是2个不同的人不能共享相同的地址。在这种情况下，您将不得不使用附加的 EntityAddress表，例如：

The main limit is that 2 different persons cannot share the same adress. In such a situation, you'll have to go with an additional "EntityAddress" table, like this:

CREATE TABLE ADDRESS
(
    ID INT IDENTITY PRIMARY KEY NOT NULL, 
    .... (your adress fields here)
)

CREATE TABLE ENTITY_ADDRESS
(
    ID INT IDENTITY PRIMARY KEY NOT NULL
    id_Address .... NOT NULL,
    id_Person .... NULL,
    id_Institution ... NULL, 
    id_Factory .... NULL
)

最后一个模型允许您共享一个地址

The last model allows you to share for example one adress for multiple persons working in the same institution.

但是：根据我的说法，更好的解决方案是将您的不同实体合并为一张桌子。然后，您将需要：

BUT: according to me, the 'better' solution would be to merge your different entities into one table. You will then need:

1. 为所有实体制作的实体表


2. 实体类型表，其中将包含不同的实体类型。
   在您的情况下，您至少有3行：人员，工厂，
   机构

如果每个实体就足够了，您可以将地址详细信息用作实体表的属性。

If one adress per entity is enough, you could go for the address details as properties of the Entity table.

如果按实体需要多个地址，则必须使用地址具有Id_Entity作为外键的表。

If you need multiple addresses by entity, you'll have to go with the Addresses Table with an Id_Entity as a foreign key.

如果要在多个实体之间共享一个地址，则每个实体都可能具有多个地址（两者之间的多对多关系实体和地址），那么除了实体表和地址表之外，您还需要使用EntityAddres表。

If you want to share one adress among multiple entities, each entity having potentially multiple adresses (a many-to-many relation between entities and adresses), then you will need to go for the EntityAddres table in addition to the Entity and Address Tables.

您在这些模型之间的选择将取决于您的需求和您的业​​务规则。

Your choice between these models will depend on your needs and your businness rules.

这篇关于一个地址表可用于许多实体？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！