sql - 当表中的每一列都是外键时，这是好事还是矫枉过正？

Question

我正在构建一个可能的车辆数据库，其中每个条目都有一个制造商、型号、年份和引擎。

我将其分为品牌(福特、雪佛兰、宝马等)和型号(Impala、Camaro、F-150 等)和年份(1920、... 2012)和发动机(327、350 等)的表格。

由于我现在为每个制造、型号、年份和引擎都有一个表，并且它们每个都可以有唯一的主键，因此主“MakesModelsAndYears”表中的每一行仅由四个外键组成。

这是矫枉过正，还是真的比我创建唯一索引的大表更有效地存储？我对“一张大 table ”方法的担忧是，像 1970 年这样的年份会重复很多次(1970 年雪佛兰 Impala、1969 年雪佛兰 Camaro 等)，就像拥有模型甚至引擎一样。

感谢您的任何指导!



跟进:

对于接下来的人，我将反馈纳入了答案并得出了这个模式。该图像没有详细显示 FK，但它们实际上是答案所建议的:

Answer 1

雪佛兰不生产野马。福特在 1960 年没有制造野马。你的结构会允许很多废话。

问题不在于每一列都是外键；这没有什么问题。问题是外键是错误的。

I split that into a tables of Makes (Ford, Chevrolet, BMW, etc) and Models (Impala, Camaro, F-150, etc) and Years (1920, ... 2012) and Engines (327, 350, etc).

这就是为什么他们错了。当您规范化关系时，您从关系开始，确定候选键，并计算出函数依赖关系。仅仅为每一列制作单列“查找”表并不是规范化，它不会以所需的方式限制您的数据。 (在这种特殊情况下，约束是缺失的部分，而不是对 5NF 的规范化。)

Make       Model   Yr    Engine
--
Ford       F-150   2012  3.7L V6
Ford       F-150   2012  3.5L V6 EcoBoost
Ford       F-150   2012  5.0L V8
Ford       F-150   2012  6.2L V8
Ford       F-150   2011  3.7L V6
Ford       F-150   2011  3.5L V6 EcoBoost
Ford       F-150   2011  5.0L V8
Ford       F-150   2011  6.2L V8
Chevrolet  Camaro  2012  3.6L V6
Chevrolet  Camaro  2011  3.6L V6
Chevrolet  Camaro  2011  6.2L V8
Chevrolet  Camaro  1980  229ci V6
Chevrolet  Camaro  1980  267ci V8
Chevrolet  Camaro  1980  305ci V8
Cadillac   CTS     2004  3.6L V6
Vauxhall   Astra   1979  1.3L
Vauxhall   Astra   1979  1.6L
Vauxhall   Astra   1979  1.8L
Opel       Astra   1979  1.5L
Opel       Astra   1979  2.0L

应该清楚，唯一的候选键是 {Make, Model, Yr, Engine}。所以这个表都是key，没有非主属性。

要将“查找”表添加为数据约束，仅说在第一列中您必须从 {Ford、Chevrolet、Cadillac、Vauxhall、Opel} 中进行选择，而在第二列中您必须从 { 中进行选择是不够的F-150、Camaro、CTS、Astra}。品牌和型号的正确“查找”表包括品牌和型号；您可以选择 {福特 F-150、雪佛兰 Camaro、凯迪拉克 CTS、沃克斯豪尔 Astra、欧宝 Astra}。 (在这种情况下，它更进一步。请参阅下面的表 model_years。)

create table makes (
  make varchar(25) primary key
);

insert into makes values
('Ford'),
('Chevrolet'),
('Cadillac'),
('Vauxhall'),
('Opel');

create table models (
  make varchar(25) not null references makes (make),
  model varchar(25) not null,
  primary key (make, model)
);

insert into models values 
('Ford', 'F-150'),
('Chevrolet', 'Camaro'),
('Cadillac', 'CTS'),
('Vauxhall', 'Astra'),
('Opel', 'Astra');

create table model_years (
  make varchar(25) not null,
  model varchar(25) not null,
  year integer not null check (year between 1900 and 2050),
  primary key (make, model, year),
  foreign key (make, model) references models (make, model)
);

insert into model_years values
('Ford', 'F-150', 2012),
('Ford', 'F-150', 2011),
('Chevrolet', 'Camaro', 2012),
('Chevrolet', 'Camaro', 2011),
('Chevrolet', 'Camaro', 1980),
('Cadillac', 'CTS', 2004),
('Vauxhall', 'Astra', 1979),
('Opel', 'Astra', 1979);

create table model_year_engines (
  make varchar(25) not null,
  model varchar(25) not null,
  year integer not null,
  engine varchar(25) not null,
  primary key (make, model, year, engine),
  foreign key (make, model, year) references model_years (make, model, year)
);

insert into model_year_engines values
('Ford', 'F-150', 2012, '3.7L V6'),
('Ford', 'F-150', 2012, '3.5L V6 EcoBoost'),
('Ford', 'F-150', 2012, '5.0L V8'),
('Ford', 'F-150', 2012, '6.2L V8'),
('Ford', 'F-150', 2011, '3.7L V6'),
('Ford', 'F-150', 2011, '3.5L V6 EcoBoost'),
('Ford', 'F-150', 2011, '5.0L V8'),
('Ford', 'F-150', 2011, '6.2L V8'),
('Chevrolet', 'Camaro', 2012, '3.6L V6'),
('Chevrolet', 'Camaro', 2011, '3.6L V6'),
('Chevrolet', 'Camaro', 2011, '6.2L V8'),
('Chevrolet', 'Camaro', 1980, '229ci V6'),
('Chevrolet', 'Camaro', 1980, '267ci V8'),
('Chevrolet', 'Camaro', 1980, '305ci V8'),
('Cadillac', 'CTS', 2004, '3.6L V6'),
('Vauxhall', 'Astra', 1979, '1.3L'),
('Vauxhall', 'Astra', 1979, '1.6L'),
('Vauxhall', 'Astra', 1979, '1.8L'),
('Opel', 'Astra', 1979, '1.5L'),
('Opel', 'Astra', 1979, '2.0L');

除非它的行首先存在于 model_years 中，否则任何引擎都不能进入该表。没有年份可以进入 model_years，除非它的行首先存在于模型中。除非它的行首先存在于制造商中，否则任何行都不能进入模型。

您可以为使用 ON UPDATE CASCADE 做一个很好的案例在这样的模式中。你也可以很好地说明不使用它。 Oracle 不支持 ON UPDATE CASCADE ，这就是您看到 ID 号充斥 Oracle 表的原因之一，也是您有时看到人们说“主键值绝不能更改”的原因之一。

这些是实现已知需求所需的表类型。