Нормализация затрудняет объединение нескольких таблиц.

У меня была таблица для магазинов, содержащая название и адрес магазина. После некоторого обсуждения мы теперь нормализуем таблицу, помещая адреса в отдельные таблицы. Это делается по двум причинам:

  1. Увеличить скорость поиска магазинов по расположению/адресу
  2. Увеличьте время выполнения проверки названий улиц с ошибками с помощью алгоритма Левенштейна при импорте магазинов.

Новая структура выглядит так (не обращайте внимания на опечатки):

country;
+--------------------+--------------+------+-----+---------+-------+  
| Field              | Type         | Null | Key | Default | Extra |  
+--------------------+--------------+------+-----+---------+-------+  
| id                 | varchar(2)   | NO   | PRI | NULL    |       |  
| name               | varchar(45)  | NO   |     | NULL    |       |  
| prefix             | varchar(5)   | NO   |     | NULL    |       |  
+--------------------+--------------+------+-----+---------+-------+  

city;
+--------------------+--------------+------+-----+---------+-------+  
| Field              | Type         | Null | Key | Default | Extra |  
+--------------------+--------------+------+-----+---------+-------+  
| id                 | int(11)      | NO   | PRI | NULL    |       |  
| city               | varchar(50)  | NO   |     | NULL    |       |  
+--------------------+--------------+------+-----+---------+-------+  

street;
+--------------------+--------------+------+-----+---------+-------+  
| Field              | Type         | Null | Key | Default | Extra |  
+--------------------+--------------+------+-----+---------+-------+  
| id                 | int(11)      | NO   | PRI | NULL    |       |  
| street             | varchar(50)  | YES  |     | NULL    |       |  
| fk_cityID          | int(11)      | NO   |     | NULL    |       |  
+--------------------+--------------+------+-----+---------+-------+  

address;
+--------------------+--------------+------+-----+---------+-------+  
| Field              | Type         | Null | Key | Default | Extra |  
+--------------------+--------------+------+-----+---------+-------+  
| id                 | int(11)      | NO   | PRI | NULL    |       |  
| streetNum          | varchar(10)  | NO   |     | NULL    |       |  
| street2            | varchar(50)  | NO   |     | NULL    |       |  
| zipcode            | varchar(10)  | NO   |     | NULL    |       |  
| fk_streetID        | int(11)      | NO   |     | NULL    |       |  
| fk_countryID       | int(11)      | NO   |     | NULL    |       |  
+--------------------+--------------+------+-----+---------+-------+  
*street2 is for secondary reference or secondary address in e.g. the US.

store;
+--------------------+--------------+------+-----+---------+-------+  
| Field              | Type         | Null | Key | Default | Extra |  
+--------------------+--------------+------+-----+---------+-------+  
| id                 | int(11)      | NO   | PRI | NULL    |       |  
| name               | varchar(50)  | YES  |     | NULL    |       |
| street             | varchar(50)  | YES  |     | NULL    |       |    
| fk_addressID       | int(11)      | NO   |     | NULL    |       |  
+--------------------+--------------+------+-----+---------+-------+  
*I've left out address columns in this table to shorten code

Новые таблицы были заполнены правильными данными, и осталось только добавить внешний ключ address.id в таблицу store.

Следующий код правильно перечисляет все названия улиц:

select a.id, b.street, a.street2, a.zipcode, c.city, a.fk_countryID
from address a
left join street b on a.fk_streetID = b.id
left join city c on b.fk_cityID = c.id
  1. Как я могу обновить fk_addressID в таблице store?
  2. Как я могу перечислить все магазины с правильным адресом?
  3. Является ли эта нормализация плохой, учитывая причины, указанные выше?

ОБНОВЛЕНИЕ

Похоже, что следующий код перечисляет все магазины с правильным адресом, однако он немного медленный (у меня около 2000 магазинов):

select a.id, a.name, b.id, c.street
from sl_store a, sl_address b, sl_street c
where b.fk_streetID = c.id
and a.street1 = c.street
group by a.name
order by a.id

mysql join normalization
person Steven    schedule 22.11.2011    source источник
comment
Я думаю, что Джефф сказал что-то вроде нормализовать, пока не станет больно, денормализовать, пока не заработает в своем блоге ... так что я думаю, что они так и сделали.   -  person Matt Fenwick    schedule 22.11.2011
comment
Это правда :) У тебя есть ссылка на его блог?   -  person Steven    schedule 22.11.2011
comment
codinghorror.com/blog/2008/07/   -  person Bill Karwin    schedule 23.11.2011

Ответы (1)


arrow_upward
1
arrow_downward

Я не буду говорить о орфографических ошибках. Поскольку вы импортируете данные, опечатки лучше обрабатываются в промежуточной таблице.

Давайте посмотрим на эту немного упрощенную версию.

create table stores
(
  store_name varchar(50) primary key,
  street_num varchar(10) not null,
  street_name varchar(50) not null,
  city varchar(50) not null,
  state_code char(2) not null,
  zip_code char(5) not null,
  iso_country_code char(2) not null,
  -- Depending on what kind of store you're talking about, you *could* have
  -- two of them at the same address. If so, drop this constraint.
  unique (street_num, street_name, city, state_code, zip_code, iso_country_code)
);  

insert into stores values 
('Dairy Queen #212',  '232', 'N 1st St SE',   'Castroville',  'CA', '95012', 'US'),
('Dairy Queen #213',  '177', 'Broadway Ave',  'Hartsdale',    'NY', '10530', 'US'),
('Dairy Queen #214', '7640', 'Vermillion St', 'Seneca Falls', 'NY', '13148', 'US'),
('Dairy Queen #215', '1014', 'Handy Rd',      'Olive Hill',   'KY', '41164', 'US'),
('Dairy Mart #101',   '145', 'N 1st St SE',   'Castroville',  'CA', '95012', 'US'),
('Dairy Mart #121',  '1042', 'Handy Rd',      'Olive Hill',   'KY', '41164', 'US');

Хотя многие люди твердо верят, что почтовый индекс определяет город и штат в США, это не так. Почтовые индексы связаны с тем, как перевозчики прокладывают свои маршруты, а не с географией. Некоторые города находятся на границе штатов; Маршруты с одним почтовым индексом могут пересекать границы штатов. Об этом знает даже Википедия, хотя их примеры могут быть устаревшими. (Маршруты доставки постоянно меняются.)

Итак, у нас есть таблица с двумя ключами-кандидатами,

  • {store_name} и
  • {street_num, street_name, city, state_code, zip_code, iso_country_code}

У него нет неключевых атрибутов. Я думаю, что эта таблица находится в 5NF. Что вы думаете?

Если бы я хотел повысить целостность данных для названий улиц, я мог бы начать с чего-то вроде этого.

create table street_names
(
  street_name varchar(50) not null,
  city varchar(50) not null,
  state_code char(2) not null,
  iso_country_code char(2) not null,
  primary key (street_name, city, state_code, iso_country_code)
);  

insert into street_names
select distinct street_name, city, state_code, iso_country_code
from stores;

alter table stores
add constraint streets_from_street_names
foreign key             (street_name, city, state_code, iso_country_code)
references street_names (street_name, city, state_code, iso_country_code);
-- I don't cascade updates or deletes, because in my experience
-- with addresses, that's almost never the right thing to do when a 
-- street name changes.

Вы могли бы (и, вероятно, должны) повторить этот процесс для названий городов, названий штатов (кодов штатов) и названий стран.

Некоторые проблемы с вашим подходом

Очевидно, вы можете ввести идентификационный номер улицы в США вместе с идентификатором страны для Хорватии. («Полное название» города, так сказать, — это тот факт, который вы, вероятно, захотите сохранить, чтобы повысить целостность данных. Вероятно, это также верно и для «полного названия» улицы.)

Использование идентификационных номеров для каждого бита данных значительно увеличивает количество требуемых объединений. Использование идентификационных номеров не имеет ничего общего с нормализацией. Использование идентификационных номеров без соответствующих уникальных ограничений на естественные ключи — совершенно банальная ошибка — позволяет дублировать данные.

person Mike Sherrill 'Cat Recall'    schedule 07.12.2011
comment
Спасибо за ответ. В итоге мы использовали только одну таблицу, содержащую все адреса. Это было сделано для того, чтобы избежать использования множества объединений и ускорить результаты поиска. Проверка названий улиц на опечатки, например, с помощью Левенштейн делается только при добавлении новых магазинов. Использование только одной таблицы для адресов steet создаст дубликаты, но не настолько, чтобы ее нельзя было проиндексировать и дать быстрый результат при поиске. - person Steven; 07.12.2011