为多租户应用程序扩展数据库

Question

我们正在重建一个多租户 asp.net Web 应用程序，但我在确定最合适的数据库结构时遇到了困难。我已经阅读了大量有关 SO 和 Microsoft 多租户架构文章的问题/答案，但似乎找不到我正在寻找的信息。

该应用程序有 2 种类型的最终用户：

• 将公司信息输入数据库的供应商（如地址、联系信息、保险金额等）
• 雇用供应商的公司。公司用户登录并查看供应商输入的数据。公司用户可以搜索供应商、运行报告等。

问题：

• 使用该系统的每个公司都需要从供应商那里获得不同的信息。他们都想要地址和联系信息等基本信息，但有些人会想要仅适用于该公司的任意数量的附加字段（特定保险信息或其他信息）。

我倾向于对数据库字段使用名称-值对，这样我们就可以根据每个公司的需要扩展数据库。我的问题：

• 鉴于上述信息以及名称值将使查询/过滤供应商显着复杂化这一事实，这似乎是最好的方法吗？我正在考虑的另一个选择是为每个具有该特定公司所需字段的公司创建一个单独的“附加字段”表。

非常感谢输入/答案。

Answer 1

我过去曾做过 EAV 解决方案，而且效果很好。我认为如果我理解正确的话（供应商与公司的关系是多对多/全对多），这可能适合您的情况。我在这里写了关于 EAV 的优缺点：

• EAV 到底有什么不好？

这是否适合您取决于几个因素。你提到的“附加字段”是一个相对固定的集合，还是按需添加的？如果它们是相对固定的集合，那么您可以只拥有一个访问（不是 Microsoft Access）表来说明每组列是否与该供应商-公司对相关。

CREATE TABLE dbo.Corporations
(
    CorporationID INT PRIMARY KEY,
    Name NVARCHAR(255) NOT NULL UNIQUE
    -- ... other columns ...
);

CREATE TABLE dbo.Vendors
(
    VendorID INT PRIMARY KEY,
    Name NVARCHAR(255) NOT NULL UNIQUE
    -- ... other columns ...
);


CREATE TABLE dbo.AdditionalColumnSets
(
    ColumnSetID INT PRIMARY KEY,
    Name NVARCHAR(255) NOT NULL UNIQUE -- e.g. Insurance
    -- ... other columns ...
);

CREATE TABLE dbo.AdditionalData
(
    VendorID INT, -- foreign key here
    ColumnSetID INT, -- foreign key here
    ColumnName NVARCHAR(255),
    ColumnValue NVARCHAR(2048),
    -- you may want to extend this to store string, number, date
    -- data differently
    PRIMARY KEY(VendorID, ColumnSetID, ColumnName)
);

CREATE TABLE dbo.AdditionalDataAccess
(
    CorporationID INT, -- foreign key here
    VendorID INT, -- foreign key here
    ColumnSetID INT, -- foreign key here
    HasAccess BIT NOT NULL DEFAULT (1),
    PRIMARY KEY(CorporationID, VendorID, ColumnSetID)
);

-- now, you can check for HasAccess in this table
-- you can also infer from lack of being in this table
-- whether that means they have access or they don't
-- have access to a particular column set.

-- ultimately, after you got hte base data from the
-- standard tables like Vendors, the query would look 
-- something like this, if presence in the 
-- AdditionalDataAccess table is required:

DECLARE @CorporationID INT = 1, @VendorID INT = 1;

SELECT
    ColumnName,
    ColumnValue
FROM
    dbo.AdditionalData AS ad
WHERE
    VendorID = @VendorID
    AND EXISTS
    (
        SELECT 1
            FROM dbo.AdditionalDataAccess
            WHERE ColumnSetID = ad.ColumnSetID
            AND CorporationID = @CorporationID
            AND VendorID = @VendorID
            AND HasAccess = 1
    );

-- you'll have to pivot or transform in the client to
-- see these as columns instead of rows

希望这是有用且有意义的。