如果代码本身很大/笨拙,那么您可能仍然希望使用代理项(如果可用)。
因为您只想强制类匹配,所以您的映射表可以是
ClauseID,
GroupClauseID,
Class (or possibly ClassID)
我的一个数据库中也有类似的设置(想想调查系统):
CREATE TABLE [dbo].[DataItems](
[DataItemID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[TypeRequired] [varchar](10) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_DataItems] PRIMARY KEY NONCLUSTERED
(
[DataItemID] ASC
),
CONSTRAINT [UX_DataItems_ClientAnswerFKTarget] UNIQUE CLUSTERED
(
[DataItemID] ASC,
[TypeRequired] ASC
),
CONSTRAINT [UX_DataItems_Name] UNIQUE NONCLUSTERED
(
[Name] ASC
)
)
CREATE TABLE [dbo].[ClientAnswers](
[ClientAnswersID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ClientID] [uniqueidentifier] NOT NULL,
[DataItemID] [uniqueidentifier] NOT NULL,
[TypeRequired] [varchar](10) NOT NULL,
[BoolValue] [bit] NULL,
[IntValue] [int] NULL,
[CharValue] [varchar](6500) NULL,
[CurrencyValue] [int] NULL,
[DateValue] [datetime] NULL,
CONSTRAINT [PK_ClientAnswers] PRIMARY KEY CLUSTERED
(
[ClientID] ASC,
[DataItemID] ASC
)
)
GO
ALTER TABLE [dbo].[ClientAnswers] ADD CONSTRAINT [FK_ClientAnswers_DataItems] FOREIGN KEY([DataItemID],)
REFERENCES [dbo].[DataItems] ([DataItemID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[ClientAnswers] ADD CONSTRAINT [FK_ClientAnswers_DataItems_TypesMatch] FOREIGN KEY([DataItemID],TypeRequired)
REFERENCES [dbo].[DataItems] ([DataItemID],TypeRequired)
GO
然后,我更进一步,并有更多的约束,以确保类型列匹配非空*值列