sql - 数据库/表的重组

Question

假设我有一个包含这些表的数据库

Car --> Has Dealerships --> Has Demographic --> Has Revenue

发布后，客户表示他们希望汽车具有车型类型，并且每个车型都应该有经销商

所以我的结构变成了

Car --> Has Model --> Has Dealerships --> has Demographic --> Has Revenue

我将如何迁移到第二个数据库结构？我需要切断 Car 和 Dealership 之间的外键才能正常工作。我是否只拍摄旧数据库的快照并在知道如何处理它的上下文中编写包装器？

Answer 1

我的理解是应该是Model --> Has Cars，而不是Car --> Has Model。在我看来，这似乎是对 --> 名称的更一致的使用。

因此，正确的最终关系链可能看起来像这样:

Model --> Has Cars      --> Has Dealerships --> Has Demographic --> Has Revenue

我会想象以下一系列操作:

1. 创建并填充 Models 表。此时您的架构:

   ModelCar --> Has Dealerships --> Has Demographic --> Has Revenue

2. 将 ModelID 列添加到 Cars 并根据您的业务需求使用正确的 Models 引用填充它。它会给你以下结果:

   Model --> Has Cars --> Has Dealerships --> Has Demographic --> Has Revenue

3. 添加对 Dealerships 的 ModelID 引用，通过使用目前的关系。您最终将在 Dealerships 和 Models 之间建立双重关系，直接和通过 Cars，如下所示:

   Model      -->     Has Dealerships --> Has Demographic --> Has Revenue    \               /     \-> Has Cars -/

4. 删除 Cars --> Dealership 外键，删除 Dealerships.CarID 引用列。这将是您的最终结果:

   Model --> Has Dealerships --> Has Demographic --> Has Revenue    \     \--> Has Cars