多对多，但来自多个表 [英] Many-to-Many but sourced from multiple tables

问题描述

我应该出货一个可变内容的框，并在数据库中跟踪。我的所有项目（框的内容）是不同的类型，并且要求不同的表来跟踪它们各自的信息，尽管每个项目类型具有相同的长度序列号（即PK是相同的数据类型）。我有一个盒子桌子。

所以每个项目都有一个表（〜7个表）加上框表。我想创建一个 BoxContents 表。我尝试使用两列的多对多关系中间表：一个用于 BoxID ，另一个用于 ItemBarcode ，其中 BoxID 是在 Boxes 表和 ItemBarcode 是Items表中每个PK的FK（即我尝试将多个表链接到同一列）。毫不奇怪，这没有办法。我试图插入一个项目，并且除了 ItemBarcode 之间的所有关系之外的所有FK约束被违反。

我可以建立我的关系，将几种类型的项目链接到一个表中的一个框？这是否合乎逻辑？你需要更多信息吗？

解决方案

我的第一个选择，如果 ItemBarcode 值是真正唯一的，将是：

编辑：添加了所需触发器的描述。

• 添加触发器来强制执行条形码唯一性。
  （每个项目表上的插入/更新触发器需要验证所有（新）分配的条形码不会出现在其他项目表中。）


• 使用单个 BoxId / ItemBarcode 在条形码侧没有FK关系的表，但带有触发器以确保其保持有效。
  （关联表上的插入/更新触发器需要验证项目表中是否存在条形码。每个项目表上的删除触发器需要防止或级联删除关联表中的项目。项目表上的更新触发器需要更新和更改关联表中的条形码，最后可以集成到先前项目符号中的插入/更新触发器中。）


• 考虑使用<

所有项目的strong>视图可以访问通用数据

我的第二个选择是 BoxId / ItemBarcode > n 项目类型。直截了当，但有点忙。这使得添加一个新的项目类型比它需要的更多。

我不会使用 BoxId / ItemTypeId / ItemBarcode 表。通过再次将 ItemTypeId 和 ItemBarcode 关联来对数据进行非规范化，它不允许在条形码方面，它仍然需要触发器来确保完整性。

不要害怕触发器。有一些问题，他们可以非常有效地解决。

I am supposed to be shipping out a box with variable contents and tracking this in a database. All of my items (the contents of a box) are different types and require different tables to track their respective pieces of information, although each item type has the same length serial number (i.e. PK are the same datatype). And I have a Boxes table.

So each item has a table (~7 tables) plus the box table. I want to create a BoxContents table. I tried to make a many-to-many relationship intermediate table with two columns: one for BoxID and one for ItemBarcode, where BoxID is a FK to the PK on the Boxes table and the ItemBarcode is a FK to each of the PKs on the Items tables (i.e. I tried to link multiple tables to the same column). Unsurprisingly this didn't work. I tried to insert an item and the FK constraint was violated on all but one of the ItemBarcode relationships.

How can I construct my relationships to link several types of items to one box in one table? Is this a logical approach? Do you need more information?

解决方案

My first choice, if the ItemBarcode values are truly unique, would be to:

EDIT: Added description of required triggers.

• Add triggers to enforce the barcode uniqueness. (An insert/update trigger on each item table needs to verify that all (newly) assigned barcodes do not appear in other item tables.)
• Use a single BoxId/ItemBarcode table without a FK relation on the barcode side, but with triggers to ensure it remains valid. (An insert/update trigger on the association table needs to verify that the barcodes exist in the item tables. A delete trigger on each item table needs to prevent, or cascade, deletion of items that are in the association table. An update trigger on the item tables needs to update and changed barcodes in the association table. This last may be integrated into the insert/update trigger in the prior bullet.)
• Consider using a view of all items to access common data by ItemBarcode.

My second choice would be n BoxId/ItemBarcode tables for the n item types. Straightforward, but a bit busy. It makes adding a new item type messier than it needs to be.

I would not use a BoxId/ItemTypeId/ItemBarcode table. It denormalizes the data by associating the ItemTypeId and ItemBarcode again, it doesn't allow the use of a FK on the barcode side, and it still requires triggers to ensure integrity.

Don't be afraid of triggers. There are some problems that they can address quite effectively.

这篇关于多对多，但来自多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！