我有 3 张表: 套餐、产品和服务:
create table dbo.Packages (
Id int identity not null
Name nvarchar (80) not null
)
create table dbo.Products (
Id int identity not null
Name nvarchar (80) not null
)
create table dbo.Services (
Id int identity not null
Name nvarchar (80) not null
)
我需要关联 3 个表以满足以下要求:
- 一揽子计划由产品和服务的组合组成;
- 所有产品都可以包含在一个包装中;
- 并非所有服务都可以包含在一个包中;
- 产品或服务可以包含在许多包装中;
- 一个产品或服务不能在同一包装中出现两次。
由于(3(和(5(,我不确定如何构建这个方案。
可能我需要表继承、多对多关系和 (5( 的某种索引?
有人可以就此建议我吗?
更新
Frazz 建议的一个选项是按如下方式使用组件基表,以便服务可以将组件 ID 设置为空。再多一个选择...
CREATE TABLE dbo.Packages (
id INT IDENTITY NOT NULL,
name NVARCHAR(80) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE dbo.Components (
id INT IDENTITY NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE dbo.Products (
id INT IDENTITY NOT NULL,
component_id INT NOT NULL,
name NVARCHAR(80) NOT NULL
PRIMARY KEY (id)
);
CREATE TABLE dbo.Services (
id INT IDENTITY NOT NULL,
component_id INT NULL,
name NVARCHAR(80) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE dbo.Package_Components (
package_id INT NOT NULL REFERENCES Packages(id),
component_id INT NOT NULL REFERENCES Components(id)
PRIMARY KEY (package_id, component_id)
);
以下内容应该可以解决您要求的大部分问题(在SQLFiddle上测试(:
CREATE TABLE dbo.Packages (
id INT IDENTITY NOT NULL,
name NVARCHAR(80) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE dbo.Products (
id INT IDENTITY NOT NULL,
name NVARCHAR(80) NOT NULL
PRIMARY KEY (id)
);
CREATE TABLE dbo.Services (
id INT IDENTITY NOT NULL,
name NVARCHAR(80) NOT NULL,
can_be_packaged BIT NOT NULL
PRIMARY KEY (id)
);
CREATE TABLE dbo.Package_Products (
package_id INT NOT NULL REFERENCES Packages (id),
product_id INT NOT NULL REFERENCES Products (id)
PRIMARY KEY (package_id, product_id)
);
CREATE TABLE dbo.Package_Services (
package_id INT NOT NULL REFERENCES Packages (id),
service_id INT NOT NULL REFERENCES Services (id)
PRIMARY KEY (package_id, service_id)
);
两个附加表Package_Products和Package_Services实现 (1(、(2( 和 (4( 所需的多对多关系。它们的主键强制执行 (5(。
剩下的就是您的要求(3(。您说这完全取决于服务,因此额外的 BIT 列can_be_packaged是处理要求的好方法。但是你需要强制执行它。
问题是您无法在 CHECK 约束中执行 SQL 语句。所以我看到的唯一解决方案是在Package_Services表上有一个用于插入的触发器和一个用于更新的触发器。这些触发器应在"服务"表上执行 SELECT 并检查can_be_packaged位是否为 1。