Use case : Save user favorite combination to database.
使用案例：将用户喜爱的组合保存到数据库。
A user can select
用户可以选择
1."a,b,c" as his first favorite combination.
1.“a，b，c”是他最喜欢的组合。
2."a,b" as second favorite combination.
2.“a，b”作为第二喜爱的组合。
How to design a DB table for saving this information and also should be able to retrieve by querying using used id
如何设计用于保存此信息的数据库表，还应该能够通过使用已使用的ID进行查询来检索
select * from UserFav where userId = :userId should return
[a,b,c] and [a,b]
从UserFav中选择*，其中userId =：userId应返回[a，b，c]和[a，b]
Too much redundant data with this table schema
userId combination
qw1 a,b,c
qw1 b,c
Can someone guide me to design in the best possible way.
有人可以指导我以最好的方式进行设计。
1 个解决方案
#1
6
I would store it in individual rows, not as delimited strings:
我会将它存储在单独的行中，而不是作为分隔的字符串：
Declare @favourites table (UserID char(3), fav_list_pos int, fav_pos int, fav_val char(1))
insert into @favourites values
('qw1', 1, 1, 'a')
, ('qw1', 1, 2, 'b')
, ('qw1', 1, 3, 'c')
, ('qw1', 2, 1, 'a')
, ('qw1', 2, 2, 'b')
select * from @favourites where userId = 'qw1' order by fav_list_pos, fav_pos
#1
6
I would store it in individual rows, not as delimited strings:
我会将它存储在单独的行中，而不是作为分隔的字符串：
Declare @favourites table (UserID char(3), fav_list_pos int, fav_pos int, fav_val char(1))
insert into @favourites values
('qw1', 1, 1, 'a')
, ('qw1', 1, 2, 'b')
, ('qw1', 1, 3, 'c')
, ('qw1', 2, 1, 'a')
, ('qw1', 2, 2, 'b')
select * from @favourites where userId = 'qw1' order by fav_list_pos, fav_pos