这是一个很好的位置数据库架构吗

Question

我正在开发一个特定于位置的应用程序 - 将其视为商店定位器，商店所有者在其中输入他们的地址信息，而其他用户只能看到一定范围内的附近商店。然而，从某种意义上说，它有点不同，不需要确切的位置，只需要城市/州（我知道很奇怪）。我考虑过存储位置的模式，并决定采用这个模式。

地点

id                      -- int
formatted_address       -- varchar(200)
is_point_of_interest    -- bool
name                    -- varchar(100) -- NULL
street_number           -- varchar(10)  -- NULL
street                  -- varchar(40)  -- NULL
city                    -- varchar(40)
state                   -- varchar(40)
state_code              -- varchar(3)
postal_code             -- varchar(10)
country                 -- varchar(40)
country_code            -- varchar(3)
latitude                -- float(10,6)
longitude               -- float(10,6)
last_updated_at         -- timestamp

以下是有关该应用程序的一些注意事项：

• 我想对国际地点敞开大门
• 我计划使用地理编码服务来搜索和验证店主指定的地点
• 我确实只需要纬度/经度，但显示商店信息需要其他数据。
• formatted_address 字段将包含完全格式化的地址 - 例如，Giants Stadium, 50 NJ-120, East Rutherford, NJ 07073, USA - 以允许更容易搜索存储的位置
• 可能会有很多重复字段，因为每行可能具有不同的粒度级别 - 例如，123 Main Street, City, State 12345 与 不同>Main Street, City, State 12345 因为一个有指定的街道号码，而另一个则不

知道该模式不是很标准化，但我也认为没有必要再对其进行标准化，因为位置非常复杂，其中这就是为什么我依赖稳定的地理编码服务（谷歌）。另外，我计划允许自由格式的文本输入/搜索，因此不需要任何下拉列表。

考虑到我提到的内容，有人认为有什么问题或有任何改进吗？我可以看到这张桌子变得相当大。

Answer 1

我不这么认为。这是我的两分钟概要：

这非常糟糕的标准化。至少应该将city->country移出到另一个表（并从那里标准化）。我相信邮政编码可以跨越城市边界（或者我记错了）；我不知道有这样一个跨越国界的城市。

formatted_address 是一个“优化”，应该是一个计算字段：也就是说，重新创建它的所有数据都应该存在于其他地方。 （这意味着现在不需要担心。）

快乐设计。

---

简单的“更规范化”形式只是执行上述建议：

LOCATIONS
location_id             -- int
is_point_of_interest    -- bool
name                    -- varchar(100) -- NULL
street_number           -- varchar(10)  -- NULL
street                  -- varchar(40)  -- NULL
city_id                 -- int
postal_code             -- varchar(10)
latitude                -- float(10,6)
longitude               -- float(10,6)
last_updated_at         -- timestamp

CITIES
city_id                 
name                    -- varchar
-- similarly, the following should be normalized to STATES and COUNTRIES
state                   -- varchar(40)
state_code              -- varchar(3)
country                 -- varchar(40)
country_code            -- varchar(3)

当然，CITIES可以进一步规范化，因此可以一个POSTALS表：我知道得不够关于邮政编码或应用程序域。 postal_code 充当隐式复合代理 FK 的一部分，因此它并不像它所在的那样超级可怕。但是，将其移至单独的表中可以轻松实现验证和完整性约束。

编辑：规范化 POSTALs 表是最好的，因为对于给定城市只有很少数量的邮政编码有效：不过，我不确定邮政编码和城市之间的关系，所以我无法推荐如何做到这一点。也许看看现有的使用模式？

Answer 2

既然你说“我真的只需要纬度/经度”，我鼓励你使用 2 个具有 1:1 关系的表。

在许多（大多数？）情况下，将会缓存更多的纬度/经度对，从而加快您的工作速度。如果您需要其他信息，请在需要时获取。

简短形式：不要强迫数据库通过 IO 和 RAM 移动不需要的数据

此外，这样的模式将为进一步的自然扩展敞开大门：可以通过添加其他表而不是更改现有表来链接其他信息。我认为这对于你的软件质量来说是一件好事。