这是建立关系数据库中地址信息的好方法吗？ [英] Is this a good way to model address information in a relational database?

问题描述

我想知道这是否是一个很好的设计。我有一些表需要地址信息（例如街道，邮政编码/邮政编码，国家/地区，传真，电子邮件）。有时相同的地址会重复多次。例如，可以针对供应商存储地址，然后对发送给他们的每个采购订单进行存储。供应商可能会更改其地址，任何后续采购订单应具有新地址。这比这更复杂，但这是一个示例要求。

选项1
将所有地址列作为属性放在各个表上。在供应商创建时将细节从供应商复制到PO。潜在地存储多个副本

选项2
创建单独的地址表。从供应商处获得一个外键，并将订单表交给地址表。只允许在地址表上插入和删除，因为更新可能会更改您的意图。然后我会有一些计划的任务从地址表中删除任何不再被任何引用的行，所以没有剩余的行。也可能在地址表中的所有非pk列上也有一个独特的约束，以阻止重复。

我倾向于选项2.是否有更好的方式？

编辑：我必须按照发送时的顺序保留采购订单上的地址。此外，由于可能会有一个发货地址和一个帐单地址（还有一堆其他具有地址信息的表格），所以建议更复杂一些。

过了一会儿，我会根据他们的日期删除旧的采购订单。在此之后，我正在打算垃圾收集任何不被任何引用的地址记录（否则感觉我正在创建一个泄漏）。

解决方案

我实际上把这个作为我的面试问题之一。以下是开始的好地方：

 地址
 --------- 
 AddressId（PK）
 Street1 
 ...（etc）
  

  AddressTypes 
 ------------ 
 AddressTypeId 
 AddressTypeName 
  

和

 code> UserAddresses（替换为Company，Account，不管用户）
 ------------- 
 UserId 
 AddressTypeId 
 AddressId 
  

这样，你的地址完全不知道如何使用它们，以及你的实体用户，帐户）也不直接知道地址。这完全取决于你创建的链接表（在这种情况下，UserAddresses，但是你可以做任何适合你的模型）。

一个有点矛盾的建议，一个潜在的大数据库：将主地址直接放在您的实体（在这种情况下的Users表中）以及HasMoreAddresses字段。与使用上述干净设计相比，似乎比较简单，但可以简化典型用例的编码，而非规范化可以对性能产生很大的影响。

I'm wondering if this is a good design. I have a number of tables that require address information (e.g. street, post code/zip, country, fax, email). Sometimes the same address will be repeated multiple times. For example, an address may be stored against a supplier, and then on each purchase order sent to them. The supplier may then change their address and any subsequent purchase orders should have the new address. It's more complicated than this, but that's an example requirement.

Option 1 Put all the address columns as attributes on the various tables. Copy the details down from the supplier to the PO as it is created. Potentially store multiple copies of the

Option 2 Create a separate address table. Have a foreign key from the supplier and purchase order tables to the address table. Only allow insert and delete on the address table as updates could change more than you intend. Then I would have some scheduled task that deletes any rows from the address table that are no longer referenced by anything so unused rows were not left about. Perhaps also have a unique constraint on all the non-pk columns in the address table to stop duplicates as well.

I'm leaning towards option 2. Is there a better way?

EDIT: I must keep the address on the purchase order as it was when sent. Also, it's a bit more complicated that I suggested as there may be a delivery address and a billing address (there's also a bunch of other tables that have address information).

After a while, I will delete old purchase orders en-masse based on their date. It is after this that I was intending on garbage collecting any address records that are not referenced anymore by anything (otherwise it feels like I'm creating a leak).

解决方案

I actually use this as one of my interview questions. The following is a good place to start:

Addresses
---------
AddressId (PK)
Street1
... (etc)

and

AddressTypes
------------
AddressTypeId
AddressTypeName

and

UserAddresses (substitute "Company", "Account", whatever for Users)
-------------
UserId
AddressTypeId
AddressId

This way, your addresses are totally unaware of how they are being used, and your entities (Users, Accounts) don't directly know anything about addresses either. It's all up to the linking tables you create (UserAddresses in this case, but you can do whatever fits your model).

One piece of somewhat contradictory advice for a potentially large database: go ahead and put a "primary" address directly on your entities (in the Users table in this case) along with a "HasMoreAddresses" field. It seems icky compared to just using the clean design above, but can simplify coding for typical use cases, and the denormalization can make a big difference for performance.

这篇关于这是建立关系数据库中地址信息的好方法吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！