Нормализирането затруднява обединяването на множество таблици

Имах таблица за магазини, съдържаща име и адрес на магазина. След известна дискусия сега нормализираме таблицата, като поставяме адреса в отделни таблици. Това се прави по две причини:

  1. Увеличете скоростта на търсене на магазини по местоположение / адрес
  2. Увеличете времето за изпълнение за проверка на неправилно изписани имена на улици с помощта на алгоритъма на Levenshtein при импортиране на магазини.

Новата структура изглежда така (пренебрегвайте правописните грешки):

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
Благодаря за обратната връзка. В крайна сметка използвахме само една таблица, съдържаща всички адреси. Това беше, за да се избегне използването на много присъединявания и да се ускори резултатът от търсенето. Проверка на имената на улиците за правописни грешки, като се използва напр. Levenshtein се прави само при добавяне на нови магазини. Използването само на една таблица за улични адреси ще създаде дубликати, но не толкова, че да не може да се индексира и да даде бърз резултат при търсене. - person Steven; 07.12.2011