我需要帮助改进我的数据库设计。
这个数据库设计是由旧开发人员完成的,现在我正在尝试通过在外键周围进行尝试来改进设计,以便不会出现任何孤立的表。
案例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上的记录不会更改。
请帮我评价设计:
- 如何使其更好?
- 如何应用外键(如果可能)?
- 有关数据库设计的任何建议?
- 在CASE 2中,我是否应添加“GCd”列以便使用外键?
- 如果ProductType上的ProdType必须是唯一的,我应该将其用作主键(带有GCd和ACd)还是仅需要放置UNIQUE或不放置任何限制?
注:
- 旧设计没有任何外键,所有内容都受应用程序限制。
- 我添加了第二个案例。