SQL Server数据库设计和外键混淆

Question

我需要帮助改进我的数据库设计。

这个数据库设计是由旧开发人员完成的，现在我正在尝试通过在外键周围进行尝试来改进设计，以便不会出现任何孤立的表。

案例1：

以下是故事背景：

我有 ProductGroup (PG)、ProductType (PT)、Brand (B)、Design (D) 四个表，用于保存物品的所有特定细节。然后将这四个表组装成一个表 ProductMaster (PM)，它们将被连接并形成一个 ProdCd。

当删除 PG, PT, B, G 中的任何行时，PM 中的所有相关行也将被删除。

以下是该表的示例（代码将被简化）：

CREATE TABLE [dbo].[ProductMaster](
    [ProdCd] [varchar](25) NOT NULL,
    [GCd] [varchar](15) NULL,
    [ACd] [varchar](15) NULL,
    [BCd] [varchar](15) NULL,
    [CCd] [varchar](15) NULL,
    [ProdType] [varchar](50) NULL,
    [BrandCd] [varchar](25) NULL,
    [Design] [varchar](150) NULL,
 CONSTRAINT [PK_ProductMaster] PRIMARY KEY CLUSTERED 
(
    [ProdCd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ProductGroup](
    [GCd] [varchar](15) NOT NULL,
    [GroupDesc] [varchar](150) NULL,
 CONSTRAINT [PK_ProductGroup] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ProductType](
    [GCd] [varchar](1) NOT NULL,
    [ACd] [varchar](15) NOT NULL,
    [ProdType] [varchar](50) NOT NULL,
    [TypeDesc] [varchar](150) NULL,
 CONSTRAINT [PK_ProductType] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [ACd] ASC,
    [ProdType] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Brand](
    [GCd] [char](1) NOT NULL,
    [BCd] [varchar](15) NOT NULL,
    [BrandCd] [varchar](25) NOT NULL,
    [BrandName] [varchar](75) NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [BCd] ASC,
    [BrandCd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Design](
    [GCd] [char](1) NOT NULL,
    [CCd] [varchar](15) NOT NULL,
    [DesignCd] [varchar](25) NOT NULL,
    [DesignDesc] [varchar](150) NULL,
 CONSTRAINT [PK_Design] PRIMARY KEY CLUSTERED 
(
    [GCd] ASC,
    [CCd] ASC,
    [DesignCd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

示例为：

• ProductMaster (100010010001, 1, 0001, 001, 0001, PC-10 A, ZEN, 纯白色)
• ProductGroup (1, 成品)
• ProdType (1, 0001, PC-10 A, 十英寸晚餐盘)
• Brand (1, 001, ZEN, ZEN)
• Design (1, 0001, 纯白色, 不带贴花的纯白色)

如果我删除 (1, 0001, PLAIN WHITE, NO DECAL PLAIN WHITE) 的 Design 行，则会删除 ProductMaster 中整行。

情况2：

我有一个交易表格，其中有头和详细信息。详细信息仅保留来自表格“ProductType”的“ACd”，其中“GCd”始终为1。当修改PG、PT、B、G时，交易记录也将被修改，“GCd”=1。

该表格如下：

CREATE TABLE [dbo].[PFHdr](
    [FNO] [varchar](25) NOT NULL,
    [FMO] [varchar](6) NULL,
    [FDate] [datetime] NULL,
    [GCd] [int] NULL, *This is group related to the transcation not with the PM
    [IQtyc] [float] NULL,
    [RQtyc] [float] NULL,
    [TQtyc] [float] NULL,
 CONSTRAINT [PK_PFrmHdr] PRIMARY KEY CLUSTERED 
(
    [FNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[PFDtl](
    [FNO] [varchar](25) NOT NULL,
    [ACd] [varchar](15) NOT NULL,
    [Input] [float] NULL,
    [Reject] [float] NULL,
    [Transfer] [float] NULL,
 CONSTRAINT [PK_PFrmDtl] PRIMARY KEY CLUSTERED 
(
    [FNO] ASC,
    [ACd] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

样本如下:

• PFHdr(201211-00001,00001,2012-11-26,1,10,0,10)
• PFDtl(201211-00001,0001,5,0,5)
• PFDtl(201211-00001,0002,5,0,5)

如果删除ProdType (1，0001，PC-10 A，Dinner Plate 10 Inch)的记录，则PFDtl上的记录也将被删除。但是如果删除记录ProdType (7，0001，PC-12 A，Dinner Plate 12 Inch），则PFdtl上的记录不会更改。

请帮我评价设计：

1. 如何使其更好？
2. 如何应用外键（如果可能）？
3. 有关数据库设计的任何建议？
4. 在CASE 2中，我是否应添加“GCd”列以便使用外键？
5. 如果ProductType上的ProdType必须是唯一的，我应该将其用作主键（带有GCd和ACd）还是仅需要放置UNIQUE或不放置任何限制？

注：

1. 旧设计没有任何外键，所有内容都受应用程序限制。
2. 我添加了第二个案例。

- Arnold Gunawan

---

你的设计在我看来还不错。问题是现在你能使用外键吗？如果可以，那就没问题了。但如果不能，那么你需要使用触发器。 - polin

据我所见，有相当多的基本设计缺陷，特别是在分配主键方面。主键已经像这样分配了吗？还是您计划以这种方式分配？ - Rachcha

设计已按原样制作，只是为了“能够工作”。主键就像这样，如果可以改进并减少或不产生孤立的表/记录，我想更改任何可能需要的内容。 - Arnold Gunawan

Answer 1

这里实际上有两个问题。外键是否存在？如果外键存在，它们是否被声明？根据我阅读的模式，其中有一些外键，例如ProductMaster.GCd。只是没有被声明。

声明外键的优点在于它约束数据以防止“引用完整性”丢失，即外键实例引用不存在的主键。这称为“孤立引用”。总的来说，强制执行引用完整性的优势远远大于DBMS在插入时检查完整性的成本和处理拒绝数据所需的额外编程。

如果您想向现有数据库添加引用完整性约束，则可能需要在DBMS允许您放置约束之前找到并处理“孤立引用”。这通常涉及大量使用“WHERE NOT EXISTS”结构。

很遗憾之前的开发人员没有在开始时声明外键。

- Walter Mitty

---

不，他们之前没有声明任何外键，因为他们认为如果通过应用程序控制，就不会有任何孤儿，但事实证明这是错误的。好的，那么我就需要找到这些孤儿了...在第二种情况下，我是否应该在表上放置GCd以便能够放置外键？ - Arnold Gunawan

应用程序代码未能防止孤立的外键？我感到震惊。我告诉你，我很震惊。 - Mike Sherrill 'Cat Recall'

有成千上万的应用程序员认为他们足够小心，可以完全避免数据错误，就像DBMS可以执行约束一样。其中大约有几十个程序员是正确的，其他人则接受了一次学费非常高昂的学习经历。很遗憾，有这么多人在没有学会正确方法的情况下涉足数据库建设。 - Walter Mitty

是否应该加入外键取决于信息需求。您是否拥有数据库的概念模型？概念模型以主题实体和这些实体之间的关系来表达价值和属性。一旦您拥有了概念模型，关系基本上就决定了外键应该放在哪里。如果您没有概念模型，也许可以通过反向工程现有数据库来创建一个。祝好运。 - Walter Mitty

我已经完成了概念模型的制作，并发现了一些缺陷。虽然应用程序可以处理这些缺陷，但我希望它能够通过外键来处理，因为在事务内部修改了某些项目...我应该添加主键到主表以创建外键表吗？ - Arnold Gunawan