关系数据库设计问题-类别范围标记

Question

我正在设计一个数据库，其中我有许多产品，每个产品都属于一个并且只有一个类别。

产品应该被标记，但只允许标记它们所属的类别。

到目前为止，这就是我所得到的：

​

​

假设我有以下两类：

• 智能手机
• 笔记本电脑

“智能手机”类别的标签：

• 双SIM
• GPS

“膝上型电脑”类别的标签：

• 背光键盘
• HDMI

这种设计的问题在于，数据库并不能阻止产品被另一类的标签标记:我的应用程序代码中的一个bug很容易导致笔记本电脑被标记为“双SIM”标签，这显然不是我想要的。

我希望在数据库级别使用外键和不使用触发器来防止这种情况。这有可能吗？

Answer 1

我能够在Oracle中完成以下操作。注意最后一次插入是如何失败的，我相信这就是你想要的。

CREATE TABLE product (
    id            INTEGER NOT NULL,
    category_id   INTEGER NOT NULL,
    PRIMARY KEY ( id ),
    CONSTRAINT uq_prod_cat UNIQUE ( id,category_id )
);

INSERT INTO product (
    id,
    category_id
) VALUES (
    1,
    1
);

CREATE TABLE tags (
    id            INTEGER NOT NULL,
    category_id   INTEGER NOT NULL,
    PRIMARY KEY ( id ),
    CONSTRAINT uq_tag_cat UNIQUE ( id,category_id )
);

INSERT INTO tags (
    id,
    category_id
) VALUES (
    1,
    1
);

INSERT INTO tags (
    id,
    category_id
) VALUES (
    2,
    1
);

INSERT INTO tags (
    id,
    category_id
) VALUES (
    3,
    2
);

CREATE TABLE product_tags (
    id            INTEGER NOT NULL,
    product_id    INTEGER NOT NULL,
    category_id   INTEGER NOT NULL,
    tag_id        INTEGER NOT NULL,
    PRIMARY KEY ( id ),
    FOREIGN KEY ( product_id,category_id )
        REFERENCES product ( id,category_id ),
    FOREIGN KEY ( tag_id,category_id )
        REFERENCES tags ( id,category_id )
);

INSERT INTO product_tags (
    id,
    product_id,
    category_id,
    tag_id
) VALUES (
    1,
    1,
    1,
    1
);

1 row inserted.

INSERT INTO product_tags (
    id,
    product_id,
    category_id,
    tag_id
) VALUES (
    2,
    1,
    1,
    2
);

1 row inserted.

INSERT INTO product_tags (
    id,
    product_id,
    category_id,
    tag_id
) VALUES (
    3,
    1,
    1,
    3
);

Error starting at line : 35 in command -
INSERT INTO product_tags (id, product_id, category_id, tag_id) VALUES (3, 1, 1, 3)
Error report -
ORA-02291: integrity constraint (SYS_C008023) violated - parent key not found

Answer 2

我的回答与Ashuntosh A非常相似，只不过我将创建一个单独的表，用于将标记与类别关联起来，这样模式允许标记应用于多个类别(例如，平板电脑和电话都可以有DualSim)：

​

​

--TSQL

create table ProductCategory
(
    id int primary key identity,
    name varchar(50) not null
)

create table ProductTag
(
    id int primary key identity,
    name varchar(50) not null
)

create table TagCategory
(
    tag_id int foreign key references ProductTag,
    category int foreign key references ProductCategory,
    primary key (tag_id, category)
)

create table Product
(
    id int primary key identity,
    type int foreign key references ProductCategory,
    unique (id, type)
)

create table TaggedProduct
(
    product int,
    tag int,
    type int,
    primary key (product, tag),
    foreign key (product, type) references Product (id, type),
    foreign key (tag, type) references TagCategory (tag_id, category)
)



insert ProductCategory
    select 'Laptop' union
    select 'Phone'

insert ProductTag
    select 'HDMI' union
    select 'Backlit Keyboard' union
    select 'Dual Sim' union
    select 'GPS'

insert TagCategory
    select 1, 1 union -- HDMI/LAPTOP
    select 2, 1 union -- Backlit/LAPTOP
    select 3, 2 union -- DualSim/PHONE
    select 4, 2 -- GPS/PHONE    

insert Product
    select 1 --a laptop

insert TaggedProduct
    select 1, 1, 1 --laptop has hdmi
    union select 1, 2, 1 --laptop has backlit keyboard

insert TaggedProduct select 1, 3, 1 
--fails because 'DualSim/Laptop' is not a valid category