数据库架构 - 表示位置

Question

我需要表示一些事件的位置，并为此应用程序设计数据库模式。我有两种呈现位置的方法：

方法1： 4个表：

• 国家
• 州
• 城市
• 地点（在此位置中，我有到country_id、state_id和city_id的外键）

方法2： 1张表：

• 位置，只需存储为文本的字段国家、州、城市即可（没有外部id）

你会推荐哪个方法？第一个方法可以帮助消除可能存在的不同名称，例如同一国家的不同称呼（美国、US、美利坚合众国等），并且在强制填写文本框时提供建议可能会很有用。

然而，第二种方法似乎更简单，并应该减少对数据库的查询次数。

你认为哪种更好？你知道在这种情况下的最佳实践是什么吗？例如，某些大型门户网站需要类似位置的东西（例如foursquare等）。据我所知，Facebook使用第二种方法，但是……我想听听你的意见，以及选择一种方法背后的原因。

谢谢！

- Bart

---

什么引擎？MySQL？Oracle？DB9？SqlLite？ - Book Of Zeus

会有很大的影响吗？如果是的话，使用MySQL，但如果您能指出例如Oracle的区别，那也会很有帮助... - Bart

是的，我会提供实际代码的答案。 - Book Of Zeus

Answer 1

方法1：

如果您想要一个良好的规范化数据库，这是一个很好的解决方案。您可以轻松管理所有表格，但在查询位置时需要进行3个左连接/内连接。我假设一切都被正确索引，因此您不会遇到性能问题，因为这些表格将相对较小（国家和州）以及城市中等大小（如果您只需要特定国家的所有城市）。如果您想要世界上所有的城市，那么该表将非常庞大，并且如果您没有正确地索引或连接表格，则可能会在某个时候遇到性能问题。

由于所有内容都在数据库中，因此如果您需要添加，更新或删除记录，则无需更改代码。

如果您需要添加、更新或删除任何记录，则此解决方案将非常易于维护。如果您需要更新名称（例如城市名称），则所有记录将同时更新。

如果按城市或州查找，查询将运行得更快，然后进行简单的左连接以获取名称即可。

方法2：

我个人不建议使用此方法，因为从可维护性的角度来看，这不是最佳解决方案。如果有一天您需要根据城市检索数据，则如果没有正确地进行索引，查询可能会执行缓慢。如果索引国家、州和城市，则查找速度会更快（但比第一种方法慢，因为varchar比int更慢）。此外，这增加了名称错误的风险，例如：New York VS newyork VS New Yrok。

此外，如果您需要更新城市的名称，则必须检索所有具有该名称的记录，然后更新所有这些记录。这可能需要很长时间。

例如：UPDATE locations SET city = 'New York' where city = 'newyork'; *注意：如果拼写错误，则必须验证所有记录以确保更新所有记录

以下是基于您的要求的框架（使用MYSQL）,适用于方法1：

CREATE TABLE `countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `states` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '',
  `fk_country_id` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_country_id` (`fk_country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '',
  `fk_state_id` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_state_id` (`fk_state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '',
  `fk_country_id` int(10) NOT NULL DEFAULT '0',
  `fk_state_id` int(10) NOT NULL DEFAULT '0',
  `fk_cities_id` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_country_id` (`fk_country_id`),
  KEY `fk_state_id` (`fk_state_id`),
  KEY `fk_cities_id` (`fk_state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/* This table should not have fk_country_id and fk_state_id since they are already in their respective tables. but for this requirement I will not remove them from the table */

SELECT locations.name AS location, cities.name AS city, states.name AS state, countries.name AS country from locations INNER JOIN cities ON (cities.id = fk_cities_id) INNER JOIN states ON (states.id = locations.fk_state_id) INNER JOIN countries ON (countries.id = locations.fk_country_id);
+-------------------+---------------+----------+---------------+
| location          | cty          | state    | country       |
+-------------------+---------------+----------+---------------+
| Statue of Liberty | New York City | New York | United States |
+-------------------+---------------+----------+---------------+
1 row in set (0.00 sec)

EXPLAIN:
+----+-------------+-----------+--------+----------------------------------------+---------+---------+-------+------+-------+
| id | select_type | table     | type   | possible_keys                          | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+--------+----------------------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | locations | system | fk_country_id,fk_state_id,fk_cities_id | NULL    | NULL    | NULL  | 7174 |       |
|  1 | SIMPLE      | cities    | const  | PRIMARY                                | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | states    | const  | PRIMARY                                | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | countries | const  | PRIMARY                                | PRIMARY | 4       | const |    1 |       |
+----+-------------+-----------+--------+----------------------------------------+---------+---------+-------+------+-------+

现在更新：

UPDATE states SET name = 'New York' WHERE ID = 1; //using the primary for update - we only have 1 New York City record in the DB
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

现在，如果我查找该城市的所有位置，它们都会显示为：“纽约”。

对于第二种方法：

CREATE TABLE `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '',
  `fk_country_id` varchar(200) NOT NULL default '',
  `fk_state_id` varchar(200) NOT NULL default '',
  `fk_cities_id` varchar(200) NOT NULL default '',
  PRIMARY KEY (`id`),
  KEY `fk_country_id` (`fk_country_id`),
  KEY `fk_state_id` (`fk_state_id`),
  KEY `fk_cities_id` (`fk_state_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


SELECT location, city, state, country FROM locations;
+-------------------+---------------+----------+---------------+
| location          | city          | state    | country       |
+-------------------+---------------+----------+---------------+
| Statue of Liberty | New York City | New York | United States |
+-------------------+---------------+----------+---------------+

现在更新：

UPDATE locations SET name = 'New York' WHERE name = 'New York City'; // can't use the primary key for update since they are varchars
Query OK, 0 rows affected (1.29 sec)
Rows matched: 151  Changed: 151  Warnings: 0

现在，如果我查看该城市的所有位置，不是所有位置都会显示：纽约 如您所见，它只花费了1.29秒（是的，速度很快），但是所有包含“纽约”的记录都已更新，但可能存在一些拼写错误或错误的名称等。

结论： 仅因此原因，我更喜欢采用第一种方法。

注意： 国家和州很少更改。也许您可以将它们放入代码中，而不是从数据库引用它们。这将从查询中节省2个INNER JOIN，并且在您的代码中，您只需检索国家或州的ID（如果您需要创建HTML下拉框，则相同）。

此外，您可以考虑使用像memcached、APC、reddis或其他任何您喜欢的缓存这些国家和州。

- Book Of Zeus

Answer 2

选择 #1，#2 没有被规范化，可能会导致问题。

- James M.