我正在建立一个网站,您可以在其中添加多个类别的项目.
在一个字段中存储多个类别并保持字段可搜索的最佳方法是什么?
解决方法:
您不在一个字段中存储多个类别,而是创建一个单独的表来为每个项目分配类别.与此类似:
-- create your table to store items/products
create table items
(
id int, -- this will be the PK
name varchar(10)
);
insert into items values
(1, 'product1'),
(2, 'product2');
-- create your table to store categories
create table categories
(
id int, -- this will be the PK
name varchar(50)
);
insert into categories values
(1, 'color'),
(3, 'material'),
(6, 'size');
-- create your join table to assign the categories to each item
-- this table will have a foreign key relationship to the items and categories table
create table items_categories
(
item_id int, -- both fields will be the PK
category_id int
);
insert into items_categories values
(1, 1),
(2, 3),
(2, 6);
然后,您将通过连接表来查询数据:
select i.id itemid,
i.name item,
c.name category
from items i
left join items_categories ic
on i.id = ic.item_id
left join categories c
on ic.category_id = c.id
标签:php,sql,database-design
来源: https://codeday.me/bug/20190723/1511575.html