这是在关系数据库中对地址信息进行建模的好方法吗？
我想知道这是否是一个好的设计。 我有许多需要地址信息的表（例如街道、邮政编码/邮政编码、国家/地区、传真、电子邮件）。 有时同一个地址会重复多次。 例如，可以针对供应商存储地址，然后在发送给他们的每个采购订单上存储地址。 然后，供应商可以更改其地址，任何后续采购订单都应具有新地址。 它比这更复杂，但这是一个示例要求。
选项1 将所有地址列作为属性放在各个表上。 创建采购订单时，将供应商的详细信息复制到采购订单中。 可能存储
选项 2 的多个副本 创建一个单独的地址表。 拥有从供应商和采购订单表到地址表的外键。 只允许在地址表上插入和删除，因为更新可能会超出您的预期。 然后我会有一些计划任务，从地址表中删除不再被任何内容引用的任何行，这样就不会留下未使用的行。 也许对地址表中的所有非 pk 列也有一个唯一的约束，以阻止重复。
我倾向于选项2。有更好的方法吗？
编辑：我必须保留采购订单上发送时的地址。 另外，它比我建议的要复杂一些，因为可能有送货地址和帐单地址（还有一堆其他包含地址信息的表）。
一段时间后，我会根据日期批量删除旧的采购订单。 在此之后，我打算对不再被任何内容引用的任何地址记录进行垃圾收集（否则感觉就像我正在创建泄漏）。
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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我实际上用它作为我的面试问题之一。 以下是一个很好的起点：
and
and
这样，您的地址完全不知道它们是如何使用的，并且您的实体（用户、帐户）也不直接了解有关地址的任何信息。 这完全取决于您创建的链接表（在本例中为 UserAddresses，但您可以执行适合您的模型的任何操作）。
对于可能很大的数据库，有一个有点矛盾的建议：继续将“主”地址直接放在您的实体上（在本例中为“用户”表中）以及“HasMoreAddresses”字段。 与仅使用上面的干净设计相比，这似乎很糟糕，但可以简化典型用例的编码，并且非规范化可以对性能产生很大的影响。
I actually use this as one of my interview questions. The following is a good place to start:
and
and
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.
选项2，毫无疑问。
需要记住的一些重要事项：当地址相互链接时向用户指示是设计的一个重要方面。 即公司地址与送货地址相同； 如果他们想更改送货地址，他们是否也想更改公司地址，或者他们想指定一个新的装货码头？ 这类东西，以及向用户提供这些信息并以这种粒度进行更改的能力非常重要。 这对于更新也很重要。 为用户提供“拆分”条目的粒度。 并不是说这种 UI 很容易设计；而是说这种 UI 很容易设计。 事实上，它是个婊子。 但这确实很重要； 任何不足几乎肯定会让您的用户感到非常沮丧和恼火。
还; 我强烈建议保留旧地址数据； 不要运行一个进程来清理它。 除非您的数据库非常繁忙，否则您的数据库软件将能够处理多余的数据。 真的。 我看到的关于数据库的一个常见错误是试图过度优化； 你确实想优化你的查询，但你不想优化你未使用的数据。 （同样，如果您的数据库活动非常高，您可能需要有一些东西来做到这一点，但几乎可以肯定的是，您的数据库将在表中仍然有多余数据的情况下正常工作。）在大多数情况下，它实际上更有利简单地让数据库增长而不是尝试优化它。 （从表中删除零星数据不会导致数据库大小显着减小，而当它确实发生时……那么，由此导致的重新索引可能会对数据库造成巨大的消耗。）
Option 2, without a doubt.
Some important things to keep in mind: it's an important aspect of design to indicate to the users when addresses are linked to one another. I.e. corporate address being the same as shipping address; if they want to change the shipping address, do they want to change the corporate address too, or do they want to specify a new loading dock? This sort of stuff, and the ability to present users with this information and to change things with this sort of granularity is VERY important. This is important, too, about the updates; give the user the granularity to "split" entries. Not that this sort of UI is easy to design; in point of fact, it's a bitch. But it's really important to do; anything less will almost certainly cause your users to get very frustrated and annoyed.
Also; I'd strongly recommend keeping around the old address data; don't run a process to clean it up. Unless you have a VERY busy database, your database software will be able to handle the excess data. Really. One common mistake I see about databases is attempting to overoptimize; you DO want to optimize the hell out of your queries, but you DON'T want to optimize your unused data out. (Again, if your database activity is VERY HIGH, you may need to have something does this, but it's almost a certainty that your database will work well with still having excess data around in the tables.) In most situations, it's actually more advantageous to simply let your database grow than it is to attempt to optimize it. (Deletion of sporadic data from your tables won't cause a significant reduction in the size of your database, and when it does... well, the reindexing that that causes can be a gigantic drain on the database.)
您想保留采购订单上最初地址的历史记录吗？
如果是，请选择选项 1，否则将其存储在供应商表中并将每个采购订单链接到供应商。
顺便说一句：数据库设计不佳的一个明显标志是需要自动化作业来保持数据“清理”或同步。 从这个角度来看，选项 2 可能是一个坏主意
Do you want to keep a historical record of what address was originally on the purchase order?
If yes go with option 1, otherwise store it in the supplier table and link each purchase order to the supplier.
BTW: A sure sign of a poor DB design is the need for an automated job to keep the data "cleaned up" or in synch. Option 2 is likely a bad idea by that measure
为什么地址表中的任何行会变得未使用？ 使用它们的采购订单肯定仍会指向它们吗？
在我看来，停止重复应该是首要任务，因此不需要任何清理。
Why would any of the rows on the address table become unused? Surely they would still be pointed at by the purchase order that used them?
It seems to me that stopping the duplicates should be the priority, thus negating the need for any cleanup.
我想我同意 JohnFx 的观点。
关于（蜗牛）邮件地址的另一件事是，由于您想包含国家/地区，我假设您想在国际范围内运送/邮寄，请保留地址字段大部分为自由格式文本。 当挪威没有邮政编码时，必须填写 5 位数的邮政编码确实很烦人，我们有 4 位数的邮政编码。
最好的字段是：
这应该是相当全球性的，如果美国邮政系统需要特定格式的邮政编码，则也包括该字段，但使其可选，除非选择美国作为国家/地区。 每个人都知道如何在自己的国家/地区格式化地址，所以只要保留换行符就应该没问题......
I think I agree with JohnFx..
Another thing about (snail-)mail addresses, since you want to include country I assume you want to ship/mail internationally, please keep the address field mostly freeform text. It's really annoying having to make up an 5 digit zip code when Norway don't have zip-codes, we have 4 digit post-numbers.
The best fields would be:
This should be pretty global, if the US-postal system require zip-codes in a specific format, then include that too but make it optional unless USA is selected as country. Everyone know how to format the address in their country, so as long as you keep the linebreaks it should be okay...
就订单而言，如果订单已发送，您将永远不想更新地址，因为个人（或公司）地址发生了变化。 如果订单存在问题，您需要记录订单实际发送的位置。
地址表是个好主意。 对其进行唯一约束，使得同一实体不能有重复的地址。 您可能仍然会得到它们，因为用户可能会添加另一个而不是查找它们，并且如果它们的拼写略有不同（St.而不是 Street），则唯一约束不会阻止这种情况。 将创建订单时的数据复制到订单中。 在这种情况下，您需要多个记录，因为您需要发送内容的历史记录。 只允许对表进行插入和删除对我来说没有任何意义，因为它们并不比更新更安全，而且涉及更多的数据库工作。 更新是在对数据库的一次调用中完成的。 如果您的想法中的地址发生变化，那么您必须先删除旧地址，然后插入新地址。 不仅有更多的数据库调用，而且出现代码错误的机会也增加了一倍。
In the case of orders, you would never want to update the address as the person (or company) address changed if the order has been sent. You meed the record of where the order was actually sent if there is an issue with the order.
The address table is a good idea. Make a unique constraint on it so that the same entity cannot have duplicate addresses. You may still get them as users may add another one instead of looking them up and if they spell things slightly differently (St. instead of Street) the unique constraint won't prevent that. Copy the data at the time the order is created to the order. This is one case where you want the multiple records because you need a historical record of what you sent where. Only allowing inserts and deletes to the table makes no sense to me as they aren't any safer than updates and involve more work for the database. An update is done in one call to the database. If an address changes in your idea, then you must first delete the old address and then insert the new one. Not only more calls to the databse but twice the chance of making a code error.
我见过每个使用选项 1 的系统都会遇到数据质量问题。 5 年后，30% 的地址将不再有效。
I've seen every system using option 1 get into data quality trouble. After 5 years 30% of all addresses will no longer be current.