这是在关系数据库中对地址信息建模的好方法吗? [英] Is this a good way to model address information in a relational database?
问题描述
我想知道这是否是一个好的设计.我有许多需要地址信息的表格(例如街道、邮政编码/邮政编码、国家/地区、传真、电子邮件).有时同一个地址会重复多次.例如，可以针对供应商存储一个地址，然后在发送给他们的每个采购订单上存储.供应商随后可以更改其地址，任何后续采购订单都应使用新地址.它比这更复杂，但这是一个示例要求.
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.
选项 1将所有地址列作为属性放在各个表上.将详细信息从供应商复制到创建的采购订单.可能存储多个
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
选项 2创建一个单独的地址表.有一个从供应商和采购订单表到地址表的外键.只允许在地址表上插入和删除，因为更新的变化可能超出您的预期.然后我会有一些计划任务，从地址表中删除任何不再被任何内容引用的行，因此不会留下未使用的行.也许对地址表中的所有非 pk 列也有一个唯一约束，以防止重复.
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.
我倾向于选项 2.有更好的方法吗?
I'm leaning towards option 2. Is there a better way?
我必须保留采购订单上的地址，因为它是发送时的地址.另外，我建议的有点复杂，因为可能有送货地址和帐单地址(还有一堆其他包含地址信息的表格).
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)
和
AddressTypes
------------
AddressTypeId
AddressTypeName
和
UserAddresses (substitute "Company", "Account", whatever for Users)
-------------
UserId
AddressTypeId
AddressId
这样，您的地址完全不知道它们是如何被使用的，您的实体(用户、帐户)也不直接了解地址的任何信息.这完全取决于您创建的链接表(在本例中为 UserAddresses，但您可以执行任何适合您的模型的操作).
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).
对于潜在的大型数据库的一条有点矛盾的建议:继续将主要"地址直接放在您的实体上(在本例中为用户"表中)以及HasMoreAddresses"字段.与仅使用上面的简洁设计相比，这似乎很糟糕，但可以简化典型用例的编码，并且非规范化可以对性能产生很大影响.
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屋！
