数据库设计：来自多个数据源的主键

Question

最初，我从 食物来源 1 的 Excel 文档中导入食物，它具有 VARCHAR 类型主要*（PK 示例 #FOOD0001）* （因为只有 1当时我刚刚使用自动递增 int ID 直接导入到 food 表中）

但我需要从另一个具有完全主键类型的源 Food Source 2 导入食物（ INT） （PK 示例 #25928747）

我目前有：

食物表

INT FoodId，名称

份量表

INT ServingId、FoodId、名称、大小

最好的数据库设计是什么，以便可以导入任何食物源而不影响当前的 ID或者至少有一个映射，以便可以轻松更新、删除食物等？出于性能原因，我不想将 ID 更改为 VARCHAR

我的一个想法是在我的食物表中引入一个 FoodSourceFoodId，它具有来自食物源的原始 id，这样如果食物从食物中更改/更新那么它可以很容易地在食品表中更新吗？

食品表

INT FoodId<PK>, *VARCHAR FoodSourceFoodId*, Name
      1               #FOOD0001             Food 1
      2               #FOOD0002             Food 2
      3               25928747              Food 1
      4               25928748              Food 2

类似地，我可以对服务表执行相同的操作，其中服务 ID 可能与源数据中的服务 ID 相关。

您认为这是正确的方法吗？或者你会建议其他东西吗？

Answer 1

我建议不要对同一列中两种不同类型（域）的值进行建模，特别是当相关类型映射到不同的 SQL 数据类型时。

建议：对每个源使用一个“子类型”表，包括其各自的“自然”键，并使用一个“超类型”表，以使用您的人工键 FoodId 例如来合并它们

CREATE TABLE Foods
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name IN ('Food 1', 'Food 2')), 
 UNIQUE (Name, FoodId)
);

CREATE TABLE Foods1
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name = 'Food 1'), 
 FOREIGN KEY (Name, FoodId)
    REFERENCES Foods (Name, FoodId)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 Food1_ID CHAR(9) NOT NULL UNIQUE 
    CHECK (Food1_ID LIKE '#FOOD[0-9][0-9][0-9][0-9]')
);

CREATE TABLE Foods2
(
 FoodId INTEGER NOT NULL UNIQUE, 
 Name CHAR(6) NOT NULL 
    CHECK (Name = 'Food 2'), 
 FOREIGN KEY (Name, FoodId)
    REFERENCES Foods (Name, FoodId)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 Food2_ID INTEGER NOT NULL UNIQUE
);

Answer 2

这似乎是一个不错的计划。另一个更常见的选择是创建一个支持表，其中 INT FoodID 作为外键（食品表）和 VARCHAR ID。然后，当您不再需要支持导入时，您可以扔掉该表。