代码之家 › 专栏 › 技术社区 › Zachary Scott

SQL表规范化:关于限制多对多关系中记录参与的简单问题

unique-constraint foreign-key-relationship sql

1

Zachary Scott · 技术社区 · 15 年前

产品表
价格点表(产品的一个或多个价格)

以及在任何给定版本中只能存在一个产品价格的约束。 如何构造表以反映约束?

为了说明这个限制,我有一个定价为1美元、2美元和3美元的产品。我有A,B,C,D,E,F,G,H,I的媒体区域(每个区域代表一个像哥伦布一样的地方)

product A price $1 goes to A, B, C
product A price $2 goes to D, E, F
product A price $3 goes to G, H, I

产品A价格在A、B、C中存在时为1美元,在A、B、C中不能有其他价格(2美元、3美元)

一个版本的M2M表到PricePoints,然后在M2M表上除PricePoint字段外的所有字段上放置一个唯一的索引是否可行?(在输入时考虑一下)是否有更好的方法来表示关系?

3 回复 | 直到 15 年前

1

2

Larry Lustig 15 年前

我理解你的问题有点困难。我不明白“产品A存在于A、B、C时价格为1美元,不可能存在于D、E、F、G、H、I.”的说法。为了这个答案,我假设“版本”和“媒体专区”是同一回事。

使用包含三个字段的中间定价表:产品id、版本id和价格id。此表的主键(如果选择使用增量非智能键,则为唯一索引)是(产品id、版本id)。

2

1

onedaywhen 15 年前

SQL DDL:

CREATE TABLE Products
(
 product_name CHAR(1) NOT NULL UNIQUE
);

CREATE TABLE ProductPrices
(
 product_name CHAR(1) NOT NULL
    REFERENCES Products (product_name), 
 product_price DECIMAL(19, 4) NOT NULL
    CHECK (product_price > 0), 
 UNIQUE (product_name,  product_price)
);

CREATE TABLE MediaZones
(
 zone_name CHAR(1) NOT NULL UNIQUE
);

CREATE TABLE Versions
(
 product_name CHAR(1) NOT NULL, 
 product_price DECIMAL(19, 4) NOT NULL, 
 FOREIGN KEY (product_name,  product_price)
    REFERENCES ProductPrices (product_name,  product_price), 
 zone_name CHAR(1) NOT NULL 
    REFERENCES MediaZones (zone_name), 
 UNIQUE (product_name, zone_name)
);

SQL DML(成功=良好):

INSERT INTO Products (product_name) VALUES ('A');

INSERT INTO MediaZones (zone_name) 
   VALUES ('A'), ('B'), ('C'), 
          ('D'), ('E'), ('F'), 
          ('G'), ('H'), ('I');


INSERT INTO ProductPrices (product_name, product_price) 
   VALUES ('A', 1),
          ('A', 2), 
          ('A', 3);

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'G');

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'A');

INSERT INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 1, 'Z');
 INTO Versions (product_name, product_price, zone_name) 
   VALUES ('A', 2, 'A');

etc etc

3

0

ulty4life 15 年前

除非您可以明确地说产品价格组合只允许在特定区域中使用,否则我认为最好放弃数据约束,在存储过程或其他业务层中使用业务规则约束来检查哪些数据存在,然后再尝试将产品价格区域组合添加到数据库中。