情景实例
现在假设需要做一个电商平台系统,摆在你面前的是一个存储用户配送地址的问题:一个用户可以有多个收货地址,不同的收货地址存放的收货人姓名、电话、地址都不同,但它们都指向一个用户。如何在数据库中存放这些数据才能达到最高效的使用呢?
基础方案
将所有数据存放在一个表里
create table user_info(
id char(36) primary key,
user_name varchar(30) not null,
password varchar(30) not null,
real_name varchar(8),
mobile char(11),
address varchar(150)
);
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456','张三','18920120206','河南安阳');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('cc95772b-75a2-4702-bd99-4c3b0322d606','fuliuqingfeng','123456','李四','18617297545','北京海淀');
insert into user_info (id,user_name,password,real_name,mobile,address)
values ('c63028fd-cf8d-4dac-a278-b5cc8fd61e3c','fuliuqingfeng','123456','王五','17694976949','山西大同');
数据库user_info表中存放的数据如下
我们会发现,上表中user_name和password两个字段重复,即这种表结构存在严重的字段冗余(user_name和password列),并且个人信息字段越多这一问题表现的越严重。
这时可以考虑将冗余的数据存放到一个新的表里来避免重复问题
进阶方案
创建新表存放冗余信息
create table user_info(
id char(36) primary key,
user_name varchar(30) not null,
password varchar(30) not null
)
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');
create table address(
id char(36) primary key,
user_info_id char(36),
real_name varchar(8) not null,
mobile char(11) not null,
address varchar(150) not null
)
insert into address (id,user_info_id,real_name,mobile,address)
values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address)
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address)
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');
这种表结构消除了字段冗余,但由于只是逻辑上的“外键”关系,没有任何物理关联,依然无法保证数据完整性。由于地址表只是在逻辑上使用“user_info_id”字段实现与用户信息表的联系,如果删除user_info表,那么对于地址表来说,就不能找到数据属于哪个用户,地址信息也就失去了意义。
另外,由于没有物理联系,用户可以向地址表中添加不属于用户信息表的数据,例如:
insert into address (id,user_info_id,real_name,mobile,address) values ('7da42cc6-36a6-4ad5-9998-71dbc30c8e17','xxx','xiaowang','18338970095','北京东城区')
这条数据并不能找到其所属用户
最佳方案
通过使用外键,使用户信息表和地址表生成物理上的关联(地址表引用用户信息表中的id字段)。
create table user_info(
id char(36) primary key,
user_name varchar(30) not null,
password varchar(30) not null
)
insert into user_info (id,user_name,password) values ('51b28fe1-4ebf-41ac-a17b-d5e276861fd0','fuliuqingfeng','123456');
create table address(
id char(36) primary key,
user_info_id char(36),
real_name varchar(8) not null,
mobile char(11) not null,
address varchar(150) not null,
constraint address_user_info_id_fk foreign key(user_info_id) references user_info(id)
)
insert into address (id,user_info_id,real_name,mobile,address)
values ('bfb9472a-7911-4e6f-a479-3b719454ebab','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address)
values ('5227c6b9-45a2-44aa-8ac0-1f63a38d3b65','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address)
values ('30b8584b-aa6a-4516-a623-03f487058586','51b28fe1-4ebf-41ac-a17b-d5e276861fd0','王五','17694976949','山西大同');
这种方案为user_info_id添加了外键,指向user_info表的主键,该约束起到了保护数据完整性的作用:如果删除的用户信息id已经在address表中使用,则该条数据无法删除;并且也无法向address表中添加用户id不存在的地址信息。