Схема базы данных - представление местоположения

Мне нужно будет представлять местоположение некоторых событий, и я разрабатываю схему базы данных для этого приложения. У меня есть два подхода для представления местоположения:

подход 1: 4 таблицы:

Страна
States
Города
Местоположение (в местоположении у меня есть внешний ключ для country_id, state_id и city_id)

подход 2: 1 таблица:

Местоположения и есть просто поля Страна, Штат, город, которые хранятся как текст (без иностранных идентификаторов)

Какой подход вы бы порекомендовали? первый из них поможет устранить возможные различные имена, например. той же страны (usa, us, united states и т.д.) и может быть полезным при предоставлении предложений при написании текстовых полей, которые, вероятно, будут обязательными.

однако второй подход кажется, что он позволит сохранить все гораздо более простым и сократить количество запросов к базе данных.

Какой, по вашему мнению, лучше? Знаете ли вы, какие лучшие практики в этом случае? Например. как он сделал некоторые большие порталы, где им также нужно что-то вроде местоположения (например, foursquare и т.д.). Afaik facebook использует второй подход, но... Я хочу услышать ваши мнения и, возможно, причины, по которым вы бы выбрали один подход над другим.

Спасибо!

Ответ 1

Подход №1:

Это хорошее решение, если вам нужна хорошая нормальная база данных . Вы можете легко управлять всеми вашими таблицами, но при запросе местоположения вам придется иметь 3 левого/внутреннего соединения. Я предполагаю, что все индексировано правильно, поэтому у вас не будет действительно проблем с производительностью, поскольку эти таблицы будут относительно небольшими (страна и штаты) и средним размером для городов (если вы хотите, чтобы все города были только для определенной страны). Если вы хотите, чтобы во всех городах мира таблица была огромной, и в какой-то момент у вас может быть проблема с производительностью, если вы не индексируете или не присоединяетесь к таблице правильно.

Поскольку все в базе данных, вам не нужно менять код, если вам нужно добавить, обновить или удалить запись.

Если вам нужно добавлять, обновлять или удалять записи, это решение будет очень легко поддерживать. Если вам нужно обновить имя (например, название города), и все записи будут обновляться сразу.

Запросы будут выполняться быстрее, если вы посмотрите по городу или штату, будет быстро, тогда простое левое соединение, чтобы получить имя, сделает трюк.

Подход № 2:

Я лично не рекомендовал бы этого, потому что для удобства обслуживания это не лучшее решение. Если когда-нибудь вам нужно будет получить данные на основе города, ваш запрос может быть медленным, если вы не индексируете его правильно. Если вы указали страну, штат, город, то она будет быстрее для поиска (но медленнее, чем первый подход, поскольку varchar медленнее, чем int для индексирования). Кроме того, вы увеличиваете риск ошибок для имен, например: New York VS newyork VS New Yrok.

Кроме того, если вам нужно обновить название города, вам придется отыскать все записи с таким именем, а затем обновить все эти записи. Это может занять много времени.

например: UPDATE местоположения SET city = 'Нью-Йорк', где city = 'newyork'; * note: также, если у вас есть орфографические ошибки, вам нужно будет проверить ВСЕ записи, чтобы убедиться, что вы обновили все записи.

Здесь скелет, основанный на вашем требовании (с использованием 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

Теперь, если я посмотрю все свои местоположения в этом городе, все скажут: Нью-Йорк

Для подхода # 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

Теперь, если я посмотрю все мои местоположения в этом городе, НЕ все скажут: Нью-Йорк

Как вы можете видеть, потребовалось 1.29 секунд (да, это быстро), но все записи, которые имеют "Нью-Йорк", были обновлены, но, возможно, там были некоторые орфографические или неправильные имена и т.д....

Вывод: По этой причине я скорее пойду с первым подходом.

Примечание: Страна и государства редко меняются. Возможно, вы можете использовать их в своем коде и не ссылаться на них из базы данных. Это сэкономит 2 INNER JOIN из запроса, а в вашем коде вы просто вернете идентификатор страны или штата (то же самое, если вам нужно создать раскрывающееся окно HTML).

Кроме того, вы можете подумать о кешировании этих стран и штатов, используя как memcached, APC, reddis или любой другой, который вы предпочитаете.

Ответ 2

Перейдите С# 1, # 2 не нормируется, что может вызвать проблемы.