SQL建模错误－－逗号分隔值

最近帮一个客户分析SQL语句的问题，大致经过如下

 

场景：

　　委托方有一个用于追踪他们产品的系统，每个产品都会卖给许多客户；但是客户又被从业务上分成两类，一类是带有合作伙伴性质的，这个

　　合作伙伴通常会给予产品一些建设性的意见，和问题反馈；还有一类就是普通客户了。

 

程序的第一个版本

create table customer(
    id int not null auto_increment, -- 客户id
    name varchar(16),-- 客户名
    constraint pk__customer__id primary key(id));
-- customer表用于记录客户的信息

create table product(
    id int not null auto_increment, -- 产品id
    name varchar(32), -- 产品名
    partner int not null, -- 合作伙伴id 
    constraint fk__product__partner foreign key(id) references customer(id),
    constraint pk__product__id primary key(id));
-- product表用于记录产品信息

-- 比如说目前的客户有google,facebook,apple; 它们都用了atlas-1.23这个产品;但只有google作为atlas-1.23的合作伙伴
insert into customer(name) values('google'),('facebook'),('apple');

-- 查询customer表中的信息
select * from customer;
+----+----------+
| id | name     |
+----+----------+
|  1 | google   |
|  2 | facebook |
|  3 | apple    |
+----+----------+

-- google 作为atlas-1.23这个产品的合作伙伴
insert into product(name,partner) values('atlas-1.23',1);

这个数据库的逻辑结构有一个问题，就是一个产品是有一个合作伙伴，为了迎合业务数据库的逻辑结构有了第二个版本

 

程序的第二版

create table customer(
    id int not null auto_increment, -- 客户id
    name varchar(16),-- 客户名
    constraint pk__customer__id primary key(id));
-- customer表用于记录客户的信息

create table product(
    id int not null auto_increment, -- 产品id
    name varchar(32), -- 产品名
    partner varchar(32), -- 合作伙伴名
    constraint pk__product__id primary key(id));
-- product表用于记录产品信息

-- 比如说目前的客户有google,facebook,apple; 它们都用了atlas-1.23这个产品;但只有google作为atlas-1.23的合作伙伴
insert into customer(name) values('google'),('facebook'),('apple');

-- 查询customer表中的信息
select * from customer;
+----+----------+
| id | name     |
+----+----------+
|  1 | google   |
|  2 | facebook |
|  3 | apple    |
+----+----------+

-- google 作为atlas-1.23这个产品的合作伙伴
insert into product(name,partner) values('atlas-1.23','google');

-- 
select * from product;
+----+------------+---------+
| id | name       | partner |
+----+------------+---------+
|  1 | atlas-1.23 | google  |
+----+------------+---------+

-- 把facebook也设置成atlas-1.23这个产品的合作伙伴
update product set partner=concat(partner,',','facebook') where name='atlas-1.23';

-- 查看atlas-1.23中是否包涵有google & facebook
select * from product;
+----+------------+-----------------+
| id | name       | partner         |
+----+------------+-----------------+
|  1 | atlas-1.23 | google,facebook |
+----+------------+-----------------+

这个看是完成了业务上的要求，但是它招来了魔鬼

select * from product;
+----+------------+------------------------+
| id | name       | partner                |
+----+------------+------------------------+
|  1 | atlas-1.23 | google,facebook        |
|  2 | alano      | facebook,google,Google |
+----+------------+------------------------+

　　1、这会引起常用的b-tree索引，hash索引失去作用如：select name from product where partner like '%google%';

　　2、数据的准确性有问题，因为你无法保证不出现google,Google这样的值存在；

　　3、可扩展性并不强，也就是说如果合作伙伴足够多那么它就会超过varchar(32)的范围；

那么这个要怎么改进呢？

程序的第三个版本就出来了

create table customer(
    id int not null auto_increment, -- 客户id
    name varchar(16),-- 客户名
    constraint pk__customer__id primary key(id));
-- customer表用于记录客户的信息


create table product(
    id int not null auto_increment, -- 产品id
    name varchar(32), -- 产品名
    constraint pk__product__id primary key(id));
-- product表用于记录产品信息

create table product_partner(
    id int not null auto_increment primary key,
    product_id int not null,
    customer_id int not null,
    constraint fk__product_id foreign key(id) references product(id),
    constraint fk__customer_id foreign key(id) references customer(id));
-- product_partner 表用于保存一个product对应的partner。

insert into customer(name) values('googl');
insert into product(name) values('atlas-1.23');

select * from product;
+----+------------+
| id | name       |
+----+------------+
|  1 | atlas-1.23 |
+----+------------+
select * from customer;
+----+-------+
| id | name  |
+----+-------+
|  1 | googl |
+----+-------+

insert into product_partner(product_id,customer_id) values(1,1);

select * from product_partner;
+----+------------+-------------+
| id | product_id | customer_id |
+----+------------+-------------+
|  1 |          1 |           1 |
+----+------------+-------------+