我有一个地理表,其中包含
- 国家
- 地区(城市、城镇、村庄、岛屿、群岛)
- 地点(场地/企业 + 行政区/区/地区),例如 - 大本钟或南华克区。
有关每种地点类型的更多详细信息,我有一个相关表格。
“country_details”表适用于“country”类型的地点,对于位置也类似。
对于像“大本钟”这样的位置,它引用了其所在地的 ID(即伦敦),还引用了国家/地区(可以简单地通过国家/地区的 iso_code)
例子:
id | title | locality_id | country_iso_code |
---------------------------------------------------------|
1 | United Kingdom | null | UK |
2 | London | null | UK |
3 | Big Ben | 2 | UK |
4 | XYZ District | 2 | UK |
设想
现在,由于为了向客户发送有关大本钟的信息,我还想获取地点名称(伦敦)和国家/地区(英国),看来我唯一的两个选择是:
- 递归CTE
- JOIN 在同一张表上。
然而,一旦我们有一个包含数万条记录的表,它可能会增长到更多(几百万条),除了查询复杂性之外,我认为它也会影响性能。
问题
获得“加入”“伦敦”和“英国”等详细信息的更好选择是什么?
这两种选择都不好吗?最好重新考虑架构设计吗?
表格:
CREATE TABLE places (
id int,
type smallint, -- ['country', 'locality', 'location']
sub_type smallint, -- nullable (city, village, etc.)
-- names
title text,
-- locality
locality_name text,
locality_id
-- country
country_iso_alpha2 text, -- 'GB'
country_name text, -- 'United Kingdom'
admin_region text, -- 'England', 'Texas', .. (null for Country)
...
);
CREATE TABLE country_details(
place_id int,
place_type smallint NOT NULL CHECK (item_type=1),
iso_alpha2 text,
iso_alpha3 text,
...
PRIMARY KEY (place_id, place_type),
FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);
CREATE TABLE location_details(
place_id int,
place_type smallint NOT NULL CHECK (item_type=3),
website text,
neighborhood text,
formatted_address text,
...
PRIMARY KEY (place_id, place_type),
FOREIGN KEY (place_id, place_type) references places (place_id, place_type) ON DELETE CASCADE
);