database-design - 数据库架构问题

Question

我正在为本地城市页面设计一个数据模型，更像是对它的要求。

所以 4 个表:国家、州、城市、社区。

现实生活中的关系是:国家拥有多个国家，拥有多个城市，拥有多个社区。

在数据模型中:我们是否以相同的方式将这些与 FK 联系起来，还是将每个与每个联系起来？就像在每个表中一样，甚至会有一个 Country ID、State ID、CityID 和 NeighbourhoodID，所以每个都相互连接？其他明智的做法是从国家到达附近，我们需要在两者之间加入另外 2 张 table ？

对于城市的 IP 地址、纬度/经度等，我需要维护更多表。

Answer 1

最接近行业标准的是:每个依赖表都通过外键链接到其直接父表:

create table country
(country_id number not null
 , country_name varchar2(30)
 , constraint country_pk primary key (country_id)
 )
/
create table state
(state_id number not null 
 , state_name varchar2(30)
 , country_id number not null
 , constraint state_pk primary key (state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(city_id number not null 
 , city_name varchar2(30)
 , state_id number not null 
 , constraint city_pk primary key (city_id)
 , constraint city_state_fk foreign key (state_id)
        references state(state_id)
 )
/
create table neighbourhood
(neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , city_id number not null 
 , constraint neighbourhood_pk primary key (neighbourhood_id)
 , constraint neighbourhood_city_fk foreign key (city_id)
        references city(city_id)
 )
/

另一种在很大程度上已经失宠的方法是将子表的主键定义为复合键，包括直接父表的键:

create table state
(country_id number not null
 , state_id number not null 
 , state_name varchar2(30)
 , constraint state_pk primary key (country_id, state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(country_id number not null
 , state_id number not null 
 , city_id number not null 
 , city_name varchar2(30)
 , constraint city_pk primary key (country_id, state_id, city_id)
 , constraint city_state_fk foreign key (country_id, state_id)
        references state(country_id, state_id)
 )
/
create table neighbourhood
(country_id number not null
 , state_id number not null 
 , city_id number not null 
 , neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , constraint neighbourhood_pk primary key (country_id, state_id, city_id, neighbourhood_id)
 , constraint neighbourhood_city_fk foreign key (country_id, state_id, city_id)
        references city(country_id, state_id, city_id)
 )
/

这种方法已被弃用，因为在短期内它会创建极其笨拙的连接，而从长远来看，它会在 key 更改时造成可怕的困惑。主键不应该改变，但复合它们会产生意义。因此，当系统的数据发生变化时——比如说有一个州边界重组——对一大堆城市的变化必须级联到 Neighborhood 表和任何其他子表。呸。

你的提议是这个的替代版本:

create table state
(state_id number not null 
 , state_name varchar2(30)
 , country_id number not null
 , constraint state_pk primary key (state_id)
 , constraint state_country_fk foreign key (country_id)
        references country(country_id)
 )
/
create table city
(city_id number not null 
 , city_name varchar2(30)
 , country_id number not null
 , state_id number not null 
 , constraint city_pk primary key (city_id)
 , constraint city_country_fk foreign key (country_id)
        references country(country_id)
 , constraint city_state_fk foreign key (state_id)
        references state(state_id)
 )
/
create table neighbourhood
(neighbourhood_id number not null 
 , neighbourhood_name varchar2(30)
 , country_id number not null
 , state_id number not null 
 , city_id number not null 
 , constraint neighbourhood_pk primary key (neighbourhood_id)
 , constraint neighbourhood_country_fk foreign key (country_id)
        references country(country_id)
 , constraint neighbourhood_state_fk foreign key (state_id)
        references state(state_id)
 , constraint neighbourhood_city_fk foreign key (city_id)
        references city(city_id)
 )
/

它避免了复合键，但您仍然有级联数据问题。它还通过为不存在的关系引入外键来违反关系实践(Neighborhood 和 Country 之间没有直接关系，它通过中间链接隐含)。

从好的方面来说，正如您所指出的，这对于运行想要返回给定国家/地区的 Neighborhoods 的查询非常有帮助。我曾在一个有用的系统上工作过(实际上它使用了继承的复合键，但原理是一样的)。然而，这是一个非常专业的数据仓库，即便如此，我运行的查询也是管理员/开发人员查询，而不是应用程序查询。除非您正在处理大量数据(数百万个社区)，否则我认为跳过几个连接所带来的性能提升不值得管理这些额外列的开销。

简而言之，使用第一种方法:它简洁且标准。

编辑

"State should be optional though since not all countries have a state. Then a Country will connect with city directly."

如果属实，那将改变一切。显然，STATE 不能用作 CITY 的标识外键。因此，CITY 必须改为引用 COUNTRY。 STATE 可以是 CITY 上的可选查找。

尽管我认为大多数国家确实有一些等效的分割，例如县或部门。甚至像列支敦士登和圣马力诺这样的微型国家也有自治市(摩纳哥只有一个)。也许唯一没有的国家是梵蒂冈城。因此，请仔细考虑是否构建您的数据模型以支持一种或两种边缘情况，或者通过为诸如罗马教廷之类的异常注入(inject)人工“状态”来处理数据。这两种方法都不完全令人满意。

"all these fields will be auto-complete fields so not sure if that alters the table structure in anyway?"

没什么区别。

"But who knows, few months later we may discover some cool feature that may need country to match with neighbourhoods too."

是的，但话又说回来，你可能不会。 XP 有一个强大的原理，称为 YAGNI - You're aren't gonna need it .基本上，不要为了一些可能永远不会出现的假定 future 需求而做大量工作并使您的设计复杂化。

如果它确实到达，那么第一个解决方案是通过中间表(或表，如果您不使用 STATE 作为 CITY 的引用)连接 NEIGHBORHOOD 和 COUNTRY。仅当该查询的性能是 Teh Suck!如果您考虑调整数据模型，它会顽固地拒绝调整。