mysql - 规范化使得跨多个表的连接变得困难

Question

我有一个包含商店名称和地址的商店表。经过一番讨论，我们现在正在对表进行规范化，将地址放在单独的表中。这样做有两个原因:

1. 按位置/地址提高商店搜索速度
2. 使用 Levenshtein algorithm 增加检查拼写错误的街道名称的执行时间导入商店时。

新结构如下所示(忽略错别字):

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

新表已经填充了正确的数据，唯一剩下的就是在 store 表中添加外键 address.id。

以下代码正确列出了所有街道名称:

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. 如何更新 store 表中的 fk_addressID？
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

Answer 1

我不会谈论拼写错误。由于您要导入数据，拼写错误最好在暂存表中处理。

让我们看看这个稍微简化的版本。

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');

尽管很多人坚信邮政编码决定了美国的城市和州，但事实并非如此。邮政编码与承运人如何驾驶他们的路线有关，而不是与地理有关。一些城市横跨各州边界；单一邮政编码路线可以跨越州界线。连Wikipedia knows this ，尽管他们的例子可能已经过时了。 (送货路线不断变化。)

所以我们有一个表有两个候选键，

• {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.

您可以(并且可能应该)对城市名称、州名称(州代码)和国家/地区名称重复此过程。

您的方法存在一些问题

您显然可以输入美国街道的街道编号以及克罗地亚的国家/地区编号。 (可以说，城市的“全名”是您可能想要存储以提高数据完整性的事实。街道的“全名”可能也是如此。)

为每一位数据使用 ID 号会大大增加所需的连接数。使用 id 号码与规范化没有任何关系。使用没有对自然键的相应唯一约束的 ID 号——一个完全常见的错误——允许重复数据。