数据库设计：使用复合键作为FK，标志数据进行共享？

问题描述

假设多个供应商销售相同的产品。每个产品都有一些可能的颜色。最后，假设数据库的初始状态是它不知道这些产品或其相应的颜色。供应商将添加产品和颜色信息。

表：

  TABLE：vendor 
 ============================== 
 | vendor_id |名称| 
 -------------------------------- 
 | 1 | ABC有限公司| 
 -------------------------------- 
 | 2 | Acme Corporation | 
 -------------------------------- 
 
 TABLE：product 
 ======================= 
 | product_id |名称| 
 ------------------------- 
 | 1 |小部件1 | 
 ------------------------- 
 | 2 |小部件2 | 
 ------------------------- 
 
 TABLE：product_color_mapping 
 ==== 
 | color_id | product_id | 
 ------------------------- 
 | 1 | 1 | 
 ------------------------- 
 | 2 | 1 | 
 ------------------------- 
 | 3 | 1 | 
 ------------------------- 
 | 1 | 2 | 
 ------------------------- 
 | 4 | 2 | 
 ------------------------- 
 | 5 | 2 | 
 ------------------------- 
 
 TABLE：color 
 ====== 
 | color_id |名称| 
 ----------------------- 
 | 1 |红色| 
 ----------------------- 
 | 2 |白| 
 ----------------------- 
 | 3 |蓝色| 
 ----------------------- 
 | 4 |黄色| 
 ----------------------- 
 | 5 |绿色| 
 ----------------------- 
  

为了使 color.name 保持唯一，product_color_mapping表用于关联产品和颜色。

在上面的例子中， Widget 1 可以是 Red ， White 或 Blue 而 Widget 2 可以是 Red ，黄色或绿色。

问题1：

我需要一个 vendor_product 表来列出实际供应商销售的产品。如何在数据库中存储 ABC Limited 正在销售 Red 小部件1 ？表格如下所示：

  TABLE：vendor_product 
 ========== 
 | vendor_id | product_id | color_id | 
 ------------------------------------- 
 | 1 | 1 | 1 | 
 ------------------------------------- 
  

与此相关的问题是 product_id 和 color_id 是 product_color_mapping 表中的复合键。我不知道使用复合键作为外键的正确方法。

问题2：

如上所述，产品和颜色信息将由供应商提供。说供应商1没有产品，并输入其第一个产品：小部件1 。供应商1然后分配产品颜色红色。

• 下一个时间供应商1输入另一个产品，我想向供应商1提供 Widget 1 作为可选项目（基本上说嘿供应商1，你之前输入过此产品这个你想再次输入的产品？）。供应商1可以选择先前输入的产品或输入新产品。如果供应商1选择 Widget 1 ，那么我想说嘿供应商1，你确定了一个以前的 Widget 1 as是红色。这是新的小部件1 还红色 ？ - 从哪个供应商1可以选择 Red 或输入新颜色。




• 当供应商2来如何允许它将$ code> Widget 1 添加到其库存中，而不要在数据库中重复 Widget 1 ？




• 最后，如何识别小部件1 和 Red 作为有效信息，并将其提供给所有供应商（不仅仅是首先输入信息的供应商1）？




注意：我不是试图找到产品和颜色的解决方案。产品将具有与其相关联的多个属性（例如，尺寸，例如小，中或大）。颜色也可能具有与之相关联的几个属性 - 这些属性可能具有自己的属性等等。供应商将输入所有这些信息。

解决方案

这是一个相当常见的问题，的域，其中多个客户贡献于通用数据库结构，但不希望看到对方的数据。例如，Oracle有一些称为虚拟私人数据库的东西。实质上，列被添加到每个表，给定行的列中的值表示谁拥有该行。意见可以基于以下内容：

  CUSTOMERA：创建视图CUSTOMERAPRODUCTS作为select *从products.user ='CUSTOMERA'
 CUSTOMERB：创建视图CUSTOMERBPRODUCTS作为select *从product.user ='CUSTOMERB'
  

您可以像这样[伪语法]创建复合键（主要，外部和备用的唯一）：

 表：颜色
 vendorid INT 
 colorid INT 
 color varchar（20）
 PK =（vendorid，colorid）
 UNIQUE索引on（vendorid，color）
 
表：PRODUCTS 
 vendorid INT 
 productid INT 
 product varchar（20）
 PK =（vendorid，productid）
（vendorid，product）上的独特索引
 
 
表：PRODUCTCOLORS 
 vendorid INT 
 productid INT 
 colorid INT 
 PK =（vendorid，productid）
 UNIQUE索引on（vendorid ，color）
 
 FK（vendorid，productid）参考产品（vendorid，productid）
 FK（vendorid，colorid）引用COLORS（vendorid，colorid）
  

但是，如果您还想制定一个这样的规定（和类似的）：

 颜色值必须是唯一的，不仅在单个供应商的子集
中，而且系统范围是唯一的（例如所以只有一行包含翡翠绿）
  

你必须在COLORS表：

  UNIQUE索引on（color）
  

但是，如果表中已经存在该颜色，那么这可能会阻止供应商B将Emerald Green添加到COLORS表中，以便与其产品一起使用，但是供应商B看不到颜色因为如果一个虚拟私人数据库或该方法的一些模拟生效，那么该行将被从其视图中过滤出来。

所以，如果你的目标是要有多个数据支流流入一个共同的数据河流，每个供应商都可以自由地游泳，那么您可能会遇到一个潜在的凌乱的情况，通常需要像中心管理员那样维护COLOR和PRODUCTCATEGORY这样的表格 - 集中维护，因为这种情况通常会导致如下的数据：

 翡翠绿色
翡翠绿色
 Emmerald绿色
  

ie与支流几乎一样多的变体，因此您的独特指标变得无效。认为他们是守卫不是很漂亮你可以有这个变体的问题只有一个支流！保持这些表的理解是一个挑战，只有一个人添加数据！为了消除这种污染，需要专门的数据管理员保持警惕，并且比大多数公司愿意从事的更多的消毒批次进口。

Assume multiple vendors selling identical products. Each product has a number of possible colors. Finally, assume that the initial state of the database is that it is not aware of these products or its corresponding colors. The vendors will be adding product and color information.

The tables:

TABLE: vendor
================================
| vendor_id | name             |
--------------------------------
| 1         | ABC Limited      |
--------------------------------
| 2         | Acme Corporation |
--------------------------------

TABLE: product
=========================
| product_id | name     |
-------------------------
| 1          | Widget 1 |
-------------------------
| 2          | Widget 2 |
-------------------------

TABLE: product_color_mapping
=========================
| color_id | product_id |
-------------------------
| 1        | 1          |
-------------------------
| 2        | 1          |
-------------------------
| 3        | 1          |
-------------------------
| 1        | 2          |
-------------------------
| 4        | 2          |
-------------------------
| 5        | 2          |
-------------------------

TABLE: color
=======================
| color_id | name     |
-----------------------
| 1        | Red      |
-----------------------
| 2        | White    |
-----------------------
| 3        | Blue     |
-----------------------
| 4        | Yellow   |
-----------------------
| 5        | Green    |
-----------------------

In order for color.name to remain unique, product_color_mapping table is used to associate the product and color.

In the example above, Widget 1 can be either Red, White, or Blue while Widget 2 can be either Red, Yellow, or Green.

Issue 1:

I need a vendor_product table to list the actual products a vendor is selling. How do I store in the database that ABC Limited is selling a Red Widget 1? Would the table look like this:

TABLE: vendor_product
=====================================
| vendor_id | product_id | color_id |
-------------------------------------
| 1         | 1          | 1        |
-------------------------------------

The problem I have with this is that product_id and color_id are a composite key in product_color_mapping table. I'm not sure what the proper way is to use a composite key as a foreign key.

Issue 2:

As stated, product and color information will be supplied by the vendors. Say vendor 1 has no products and enters its very first product: Widget 1. Vendor 1 then assigns the product the color Red.

• The next time vendor 1 enters another product, I would like to provide Widget 1 as a selectable item to vendor 1 (basically saying, "Hey vendor 1, you entered this product before. Is this the product you are trying to enter again?"). Vendor 1 can then either select the previously entered product OR enter a new product. If vendor 1 selects Widget 1, I then want to say "Hey vendor 1, you identified a previous Widget 1 as being color Red. Is this new Widget 1 also Red?" -- from which vendor 1 can select Red or enter a new color.

• When vendor 2 comes along, how do I allow it to also add Widget 1 to its inventory WITHOUT having a duplicate Widget 1 in the database?

• Finally, how do I identify Widget 1 and Red as being "valid" information -- and make it available to all vendors (not just vendor 1 who entered the information in the first place)?

NOTE: I'm not trying to find a solution for product and color. Products will have several attributes associated with it (e.g. sizes, such as "Small", "Medium", or "Large"). Color may also have several attributes associated with it - and those attributes may have attributes of their own and so on. The vendors will enter all these information.

解决方案

This is a fairly common issue to be solved in a variety of domains where multiple customers contribute to a common database structure but don't want to see each other's data. Oracle, for example, has something called a Virtual Private Database. In essence a column is added to each table and the value in the column for a given row indicates who "owns" the row. Views can be based on this:

             CUSTOMERA : create view CUSTOMERAPRODUCTS as select * from products where products.user='CUSTOMERA'
             CUSTOMERB:  create view CUSTOMERBPRODUCTS as select * from products where products.user='CUSTOMERB'

You create composite keys (primary, foreign, and alternate unique) like this [pseudo-syntax]:

             Table: COLORS
             vendorid INT
             colorid INT
             color  varchar(20)
             PK = (vendorid, colorid)
             UNIQUE index on (vendorid, color)

             Table: PRODUCTS
             vendorid INT
             productid INT
             product varchar(20)
             PK = (vendorid, productid)
             UNIQUE index on (vendorid, product)


             Table: PRODUCTCOLORS
             vendorid INT
             productid INT
             colorid INT
             PK = (vendorid, productid)
             UNIQUE index on (vendorid, color)

             FK (vendorid, productid) references PRODUCTS(vendorid, productid)
             FK (vendorid, colorid) references COLORS(vendorid, colorid)

Now, however, if you also wanted to make this particular rule (and similar) a requirement:

            Color values must be unique not only within the individual vendor's subset
            but unique system-wide (e.g. so that there is only one row containing 'Emerald Green')

You would have to do this in the COLORS table:

            UNIQUE index on (color)

However, that would prevent vendor B from adding 'Emerald Green' to the COLORS table for use with their products if that particular color already existed in the table, yet vendor B could not see the color because that row would be filtered out of their views if a "virtual private database" or some analog of that approach was in effect,

So, if your goal is to have multiple data-tributaries flowing into a common data-river in which every vendor can swim freely, so to speak, then you have a potentially messy situation that typically requires tables like COLOR and PRODUCTCATEGORY to be maintained by a central administrator--centrally maintained because this situation usually results in data that looks like this:

                     Emerald Green
                     Emerald-Green
                     Emmerald Green

i.e. almost as many "variants" as there are tributaries, so your unique indexes become rather ineffectual. Isn't it pretty to think they're standing guard. You can have this variants problem with only one tributary! It's a challenge to keep these kinds of tables sane with only one person adding data! To eliminate such slop requires constant vigilance by a dedicated data administrator and far more sanitizing of batch imports than most companies are ever willing to engage in.

这篇关于数据库设计：使用复合键作为FK，标志数据进行共享？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！