Database 数据库模式 location

Approach #1:个

如果你想要满分normalized database分，这是个不错的解决方案.您可以轻松地管理所有表，但在查询位置时必须有3个左/内连接.我假设所有内容都被正确地编入索引，因此您在性能方面不会有真正的问题，因为这些表相对较小(国家和州)和中等大小的城市(如果您只想要特定国家的所有城市).如果您想要世界上所有的城市，那么这个表会很大，如果您没有正确地索引或连接表，那么在某个时候可能会出现性能问题.

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

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

如果按城市或州查找，查询将更快地运行，那么一个简单的左连接来获取名称就可以了.

Approach #2:个

我个人不推荐这样做，因为对于可维护性而言，这不是最好的解决方案.如果有一天您需要检索基于城市的数据，如果您没有正确索引，查询的执行速度可能会很慢.如果您为国家、州、城市编制索引，那么查找速度会更快(但比第一种方法慢，因为varchar的索引速度比int慢).此外，您增加了名字出错的风险，例如:New York vs New york vs New Yrok.

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

例如:更新位置设置City=‘New York’where City=‘newyork’； *注意:另外，如果拼写错误，则必须验证所有记录以确保更新所有记录

Here's a skeleton based on your requirement (using MYSQL) for approach #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

现在，如果我看一下我在那座城市的所有位置，所有人都会说:纽约

For approach #2:个

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

现在，如果我在我所有的地点寻找那个城市，并不是所有的地方都会说:New York

正如你所看到的，它花了1.29秒(是的，它很快)，但是所有有"New York"的记录都被更新了，但是可能有一些拼写错误或名字不好等等……

Conclusion:个 仅出于这个原因，我宁愿采用第一种方法.

注: 国家和州很少改变.也许您可以在代码中包含这些内容，而不要从数据库中引用它们.这将从查询中保存2个内连接，在您的代码中，您只需检索国家或州的ID(如果需要创建HTML下拉框，情况也是如此).

此外，您还可以考虑使用memcached、APC、reddi或您喜欢的任何其他方式缓存这些国家和州.