Table
數據庫架構:如果一個表是另一個表的一部分怎麼辦
我的 SQL Server 數據庫有一個名為 Lead 的初始表。此表包含有關領導者的各種資訊。它有 3 個外鍵列:IndustryId、BusinessTypeId 和 ReferenceId。我在有關表 Lead 和 Reference 的體系結構中遇到問題。
這是表 Lead 的腳本:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Lead]( [LeadId] [int] IDENTITY(1,1) NOT NULL, [LeadName] [nvarchar](100) NOT NULL, [CompanyName] [nvarchar](100) NOT NULL, [Phone] [nvarchar](50) NOT NULL, [Address] [nvarchar](max) NOT NULL, [DOB] [date] NULL, [IndustryID] [int] NOT NULL, [BusinessTypeID] [int] NOT NULL, [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_Lead_IsDeleted] DEFAULT ((0)), [ReferenceId] [int] NOT NULL, [IsLead] [bit] NOT NULL, [Email] [nvarchar](100) NOT NULL, [Website] [nvarchar](100) NOT NULL, [RepresentativeName] [nvarchar](100) NOT NULL CONSTRAINT [DF_Lead_RepresentativeName] DEFAULT (''), [IsClosed] [bit] NOT NULL CONSTRAINT [DF_Lead_IsClosed] DEFAULT ((0)), [feedback] [bit] NOT NULL CONSTRAINT [DF_Lead_feedback] DEFAULT ((0)), [feedbackMemo] [nvarchar](100) NULL, CONSTRAINT [PK_Lead] PRIMARY KEY CLUSTERED ( [LeadId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Lead] WITH CHECK ADD CONSTRAINT [FK_Lead_LeadIndustry] FOREIGN KEY([IndustryID]) REFERENCES [dbo].[Industry] ([IndustryId]) GO ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadIndustry] GO ALTER TABLE [dbo].[Lead] WITH CHECK ADD CONSTRAINT [FK_Lead_LeadReference] FOREIGN KEY([ReferenceId]) REFERENCES [dbo].[Reference] ([ReferenceId]) GO ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadReference] GO ALTER TABLE [dbo].[Lead] WITH CHECK ADD CONSTRAINT [FK_Lead_LeadType] FOREIGN KEY([BusinessTypeID]) REFERENCES [dbo].[BusinessType] ([BusinessTypeId]) GO ALTER TABLE [dbo].[Lead] CHECK CONSTRAINT [FK_Lead_LeadType] GO
這是有問題的表的腳本參考:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Reference]( [ReferenceId] [int] IDENTITY(1,1) NOT NULL, [ReferenceName] [nvarchar](100) NOT NULL, [Address] [nvarchar](max) NOT NULL, [DOB] [date] NULL, [Memo] [nvarchar](max) NOT NULL, [IsDeleted] [bit] NOT NULL, [Phone] [nvarchar](100) NOT NULL, [DateTimeAdded] [datetime] NOT NULL, [AddedByUserId] [int] NOT NULL, [DateTimeUpdated] [datetime] NULL, [UpdatedByUserId] [int] NOT NULL, CONSTRAINT [PK_Reference] PRIMARY KEY CLUSTERED ( [ReferenceId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
問題是每個潛在客戶都有一個參考,但參考可能是一個潛在客戶。在 UI 中添加新 Lead 時,會要求使用者設置其 Reference,但他可以選擇現有 Lead 作為引用新 Lead 的人,也可以選擇普通 Reference。
所以現在我不確定,設置這些表的最佳方法是什麼?我應該將它們合併到一個表 Lead 中,當它是一個簡單的參考時,我將不需要的列保留為 Null,或者保留兩個表並向參考表“LeadId”添加一個新列而不將其設為外鍵?或者也許是另一種方法。請指教。
有兩種方法:
- 正如您所建議的,選項 1 是
LeadId
在表中創建一個新的 FK 列[Reference]
,並使其可為空。這會起作用,查詢起來很容易,但是你會有一個包含很多空值的列。- 選項 2 是 3NF(第三範式)方式,第三個錶鍊接
[Reference]
到[Lead]
. 那將包含一個ReferenceId
FK 和一個LeadId
FK。您將需要在查詢中加入此表,但您不會被很多空值所困。