存储不同实体的“地址”的最佳架构是什么？

Suppose we're making a system where we have to store the addrees for buildings, persons, cars, etc.

假设我们正在建立一个系统,我们必须存储建筑物,人员,汽车等的附加物。

The address 'format' should be something like:

地址'格式'应该是这样的:

State  (From a State list)
County (From a County List)
Street (free text, like '5th Avenue')
Number (free text, like 'Chrysler Building, Floor 10, Office No. 10')

(Yes I don't live in U.S.A)

(是的,我不住在美国)

What would be the best way to store that info:

存储该信息的最佳方式是什么:

Should I have a Person_Address, Car_Address, ...

我应该有Person_Address,Car_Address,...

Or the address info should be in columns on each entity,

或者地址信息应该在每个实体的列中,

Could we have just one address table and try to link each row to a different entity?

我们可以只有一个地址表并尝试将每一行链接到另一个实体吗?

Or are there another 'better' way to handle this type of scenario?
How would yo do it?

还是有另一种“更好”的方式来处理这种情况?你会怎么做?

3 个解决方案

#1

I have seen scenarios where the Address is stored in an Address table and then there are many-to-many link tables which store links to addresses from People - there is a separate table for each so that foreign keys can be enforced. Sometimes the link table stores information about the relationship, like primary, ship-to, etc.

我已经看到了Address存储在Address表中的情况,然后有多对多的链接表存储了People的地址链接 - 每个都有一个单独的表,以便可以强制执行外键。有时,链接表存储有关关系的信息,如主要,送货到等。

I've also seen it where the address is stored in the row of a customer. This results in effectively arrays of addresses for bill-to, ship-to, etc, and it's fine that way. Having dealt with both, I think I prefer having them in their own entities, it allows you to keep history of old inactive addresses pretty easily.

我也看到了地址存储在客户行中的位置。这样可以有效地为收单方,收货方等提供地址数组,这样就可以了。处理完这两个后,我认为我更喜欢在他们自己的实体中使用它们,它允许您非常容易地保留旧的非活动地址的历史记录。

We've used this same technique for phone numbers, where people need to store varying numbers of phone numbers.

我们对电话号码使用了相同的技术,人们需要存储不同数量的电话号码。

#2

I would highly recommend reading 'Data Model Patterns - Conventions of Thought' by David C. Hay. This issue is discussed in depth by the author.
What you have in your design are two broad entities.

我强烈建议阅读David C. Hay撰写的“数据模型模式 - 思想公约”。作者深入讨论了这个问题。您在设计中拥有的是两个广泛的实体。

Address of a geographical location

地理位置的地址

A person/object that resides/belongs to the address

驻留/属于该地址的人/对象

In general, it is not a good practice to combine the address with a person or objects' details in the same table like below

通常,将地址与一个或多个对象的详细信息组合在同一个表中并不是一个好习惯

Person(personID, name, gender, addressline1, addressline2)

You could have the following entities in your design

您可以在设计中使用以下实体

Address(number, street, countyID,stateID)
Party(PartyID, Type)
Person(PersonID, name, dob, gender,...,primaryPartyID)
Car(carID, make, model, ...,primaryPartyID)

The Party is a link between person/car to an address. The primaryPartyID in person and Car tables are foreign keys to the party table. This way, you can share and address between a car and a person. In the event you want to store multiple addresses for each person, you could add a separate m:n table between person and party. The type attribuet for Party can take the following values : 'Person', 'Vehicle' etc...

党是人/车到地址之间的链接。 personPartyID in person和Car表是聚会表的外键。这样,您就可以在汽车和人之间共享和寻址。如果您想为每个人存储多个地址,您可以在人与方之间添加单独的m:n表。 Party的类型属性可以采用以下值:'人','车辆'等......

#3

I'd say to have an AddressType field that is a lookup from a Drop-Down list

我要说一个AddressType字段是一个从下拉列表中查找的字段

#1