如何防止规范化数据库明细表出现孤立记录？
我必须维护一个未正确规范化的旧数据库。 例如，有一个项目表已经增长（或者可能如雨后春笋般增长），具有 5 个或更多不同的日期列，用于项目从订购到交付日期的不同里程碑。 还有几个表，每个表都包含街道地址、邮件地址或 Web 链接的列。
我想规范化结构，创建地址、计划日期等表格，以及允许 1:N 关系（每个客户的地址、每个项目的截止日期等）的必要表格。
现在我完全不确定如何处理详细表中数据的更改。 例如，考虑更改客户送货地址。 更改地址表中的数据是不可能的，因为多个记录（可能在多个表中）可以引用该记录。 如果没有其他行与其具有外键关系，则添加新地址记录可能会使旧记录变得孤立。
我考虑了以下方法来处理这个问题：
添加新的详细记录，并在主表的更新触发器中检查是否必须删除旧的详细记录。 这需要了解与详细表有关系的所有表（在所有表中或在存储过程中）。 我不喜欢这种失去分离的感觉。 它还会涉及活动事务中的更多表。
让触发器尝试删除旧的详细记录，并捕获任何错误。 这感觉不对。
使用孤立记录，并通过定期维护任务清理所有详细信息表。
使用
处理链接到多个主表的明细表中的数据更改的首选方法是什么？ 阅读本文有什么建议吗？
I have to maintain an old database which is not properly normalized. For instance there is a project table that has grown (or maybe mushroomed) to have 5 or more different date columns, for different milestones of the project from being ordered to the delivery date. There are also several tables each with columns for street addresses, mail addresses or web links.
I would like to normalize the structure, create tables for addresses, scheduled dates and the like, and the necessary tables to allow for 1:N relations (address per customer, due date per project and so on).
Right now I'm completely unsure how to handle changes to the data in the detail tables. Consider for example the change of a customer delivery address. Changing the data in the address table is out of the question, because more than one record (possibly in more than one table) could reference that record. Adding a new address record could leave the old record orphaned if no other row has a foreign key relation to it.
I have thought about the following ways to handle this:
Add a new detail record, and check in an update trigger of the master table whether the old detail record has to be deleted. This would require knowledge about all tables that have relations to the detail table, in all of them or in a sproc. I don't like this loss of separation. It would also involve more tables in the active transaction.
Let the trigger try to delete the old detail record, and catch any errors. This just feels wrong.
Live with the orphaned record, and have a periodic maintenance task clean up all detail tables.
What is the preferred way to handle data changes in detail tables that are linked to several master tables? Any tips for reading up on this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
部分问题可能在于原始架构设计：外键指向错误的方向，将地址、电话号码等视为主数据而不是细节。 当您希望一次更新给定地址的所有使用时，这可能很方便，但根据我的经验，它总是会演变成太多困难的例外情况，例如，某个位置的一个人移动，因此您需要断开他们的链接与整个链接家庭或办公室搬迁，以便您更新现有记录。 如果您尝试在 CRUD 屏幕上向用户隐藏此详细信息，您最终会遇到这样的情况：它不会执行您想要的操作。
如果这样做只是为了折叠重复值，那么它实际上是数据库的非规范化：地址行的存在是毫无意义的。 唯一的区别是，与大多数非规范化不同，它试图获得空间效率而不是速度。 此时创建链接表只会使问题变得更加复杂。
例如，如果您希望每个联系人有多个地址，请将地址设为带有指向父联系人的外键的详细信息表，并且不必担心重复的地址值，因为它们只是值。 否则，使 Address 成为一个真实的实体：添加标题或描述字段以及 CRUD 屏幕，以便它可以作为一个实体独立存在。
Part of the problem may be the original schema design: the foreign keys point the wrong way, treating addresses, phone numbers, etc. as master instead of detail. This may be convenient when you want all uses of a given address to update at once, but in my experience it always devolves into too many difficult exceptional cases, for example one person at a location moves so you need to break their link vs an entire household or office moving so you update the existing record. If you try to hide this detail from the user on the CRUD screen, you'll end up with a situation where it just doesn't do what you want.
If it's done that way just to collapse duplicate values, it's effectively a denormalization of the database: the mere existence of the address row is meaningless. The only difference is that unlike most denormalizations, it attempts to gain space efficiency instead of speed. Creating a link table at that point is simply compounding the problem.
If you want, for example, multiple addresses per contact, make the addresses a detail table with a foreign key pointing back to the parent contact, and don't worry about duplicated address values because they're just values. Otherwise, make Address a real entity: add a title or description field and a CRUD screen so it can stand on its own as an entity.
处理孤立的记录，并定期执行维护任务来清理所有详细信息表。
Live with the orphaned record, and have a periodic maintenance task clean up all detail tables.
我认为你模糊了删除和更新的情况。
如果您有客户端 a 和客户端 b，并且两者都使用相同的地址，这将通过关系表中的记录反映出来（例如 ClientAddresses，尽管如果您要存储多个实体的地址，我确信它会比那）
我认为，如果两个客户端共享和地址，并且对于客户端 a 来说是不正确的，那么对于客户端 b 来说也是不正确的（即数据输入错误），但是如果您确定不希望客户端 a 更改对基地址信息进行修改，删除关联记录（从ClientAddresses中删除）并添加新地址。 当您从关系表（可能是从存储过程）执行删除时，检查是否有任何其他记录引用正在解除关联的地址记录，如果没有从基表中删除。
I think you are blurring the delete and update cases.
If you have client a and client b, and the both use the same address, that would be reflected by records in a relational table (say ClientAddresses, although if you are storing addresses for multiple entities, I am sure it will be more complex than that)
I would think that if two clients share and address and it is incorrect for client a it would be incorrect for client b as well (ie data entry error), but if you are sure that you do not want client a changes to the made to the base address info, remove the association record (delete from ClientAddresses) and add a new address. When you perform the delete from the relational table (presumably from a stored procedure) check to see if there are any other records referring to the address record being disassociated, if not delete from the base table.