数据库结构，用户+用户类型，其中用户可以是多个类型

我目前有一个用户表tblUser和一个用户类型表tblUserTypes。

两者通过tblUser... fkUserTypeID中的外键链接连接。

因此，目前一个用户只能是一种类型。

但是，在某些情况下，用户可以是多种类型…例如，Customer和Supplier。

对我来说，显而易见的解决方案是在tblUser和tblUserTypes之间创建一个新表，tblUser_UserTypes是一个桥接表:

[tblUser] ---< [tblUser_UserTypes] >--- [tblUserTypes]

但是，我可以看到由此产生的复杂性…例如，当导出连接到其用户类型的用户列表时，使用直接连接，我将最终得到这些用户的多行。也许可以使用PIVOT查询将每个用户记录还原为单行?

将用户导入系统似乎也有问题…我目前正在使用BCP(批量复制过程)从一个文件直接导入用户到用户表…导入文件包含一个字段"user type"，它在现有模型中工作，因为每个用户当前只能是一种类型。但是，对于多个用户类型，我不知道如何直接将BCP直接放入用户表中。

增加复杂性的是用户类型目前不是固定的…表tblUserTypes是动态的…系统的一部分是允许创建任意数量的用户类型。但是，我需要了解某些类型的用户，以便能够在更高的级别上定义业务逻辑....如。"只允许type=x的用户进入该区域"…因此，有人建议在用户类型表中有一系列标志来定义用户类型的类型(例如IsCustomer, IsSupplier)

这感觉就像一个过于复杂的烂摊子，我不知道如何继续前进。

我想把用户类型带回到表tblUser中，并完全删除其他两个表…用户表中的一系列复选框(如IsCustomer, IsSupplier)…因为这样进出口就更简单了。但是这样用户类型就不是动态的了。有趣的是，用户类型并不是完全动态的……因为如上所述，当涉及到商业登录时，我需要了解一些用户类型。

嗯，应该是两者的混合吗?我是否试图将两个功能合并为一个?也许我可以在用户表中为与业务逻辑相关的类型(例如IsCustomer, IsSupplier)设置复选框/布尔类型，并将"User Types"的上下文重命名为"User Groups"或类似的内容。

当考虑一个直接连接将导致用户被复制的结构时，我主要关心的是对导入、导出和搜索结果的影响。每个用户类型对应一行。我必须执行一个PIVOT查询才能将其恢复为每个用户一条记录，每种用户类型都有一列，不是吗?一个现实的例子是一个有300万条记录的User表，一次要导入10,000条记录……或者一次导出10,000条记录……或者在这300万条记录中搜索以检索3,000个匹配项，并将其以分页方式呈现在网页上，以便他们可以快速浏览搜索结果页面(我在搜索查询中使用ROWNUM来使用分页，我不会每次都返回全部)。

这是我关于Stack Overflow的第一个问题，如果它有点复杂或者已经列出了答案，我很抱歉…我试着搜索，但无法提出处理与多种类型的用户一起工作的复杂性的例子。

哦，如果这很重要…这是一个c# ASP。. NET应用程序与SQL Server。

---

在仔细考虑并阅读了回复后，我将使用桥接表。需求说明用户可以是多种类型，所以它将是这样的。对现有代码的影响是戏剧性的，但现在总比以后好。

我摆弄了一下表结构，在平面结构中获取数据所需的查询有点繁琐，最终需要动态SQL(因为用户类型列表是动态的)，这是我不喜欢的，但我看不到其他方法。

在下面的例子中，获取的公司是通过'事件ID'过滤的，即fkEventID

如果有更好的方法来做"平坦"，我会非常感谢任何帮助:-)

直接连接(如果每个公司有多个类型，则为多个行)

select * from tblCompany 
left join tblCompany_CompanyType on fkCompanyID = pkCompanyID
left join tblCompanyType on fkCompanyTypeID = pkCompanyTypeID
where tblCompany.fkEventID = 1

硬编码透视查询(如果有多个类型，则每个公司单行，但公司类型不是动态的)

select * from (
select tblCompany.*,tblCompanyType.CompanyType from tblCompany left join
tblCompany_CompanyType on fkCompanyID = pkCompanyID
left join tblCompanyType on fkCompanyTypeID = pkCompanyTypeID
where tblCompany.fkEventID = 1
) AS sourcequery
Pivot (count(CompanyType) for CompanyType IN ([Customer],[Supplier],[Something Else])) as CompanyTypeName

动态数据透视查询(每个公司多行并处理动态公司类型)

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @sql  AS NVARCHAR(MAX)
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(CompanyType) AS [text()]
FROM (
select CompanyType from tblCompanyType
where fkEventID = 1
) AS Y
FOR XML PATH('')),
1, 1, N'');
SET @sql = N'SELECT * FROM (
select tblCompany.*,tblCompanyType.CompanyType from tblCompany left join tblCompany_CompanyType on fkCompanyID = pkCompanyID
left join tblCompanyType on fkCompanyTypeID = pkCompanyTypeID
where tblCompany.fkEventID = 1
) AS sourcequery
Pivot (count(CompanyType) for CompanyType IN (' + @cols + ')) as CompanyTypeName
order by pkCompanyID'
EXEC sp_executesql @sql;