3pFmsvna

1Extend database for multi-tenant application
2CREATE TABLE dbo.Corporations
3(
4    CorporationID INT PRIMARY KEY,
5    Name NVARCHAR(255) NOT NULL UNIQUE
6    -- ... other columns ...
7);
8
9CREATE TABLE dbo.Vendors
10(
11    VendorID INT PRIMARY KEY,
12    Name NVARCHAR(255) NOT NULL UNIQUE
13    -- ... other columns ...
14);
15
16
17CREATE TABLE dbo.AdditionalColumnSets
18(
19    ColumnSetID INT PRIMARY KEY,
20    Name NVARCHAR(255) NOT NULL UNIQUE -- e.g. Insurance
21    -- ... other columns ...
22);
23
24CREATE TABLE dbo.AdditionalData
25(
26    VendorID INT, -- foreign key here
27    ColumnSetID INT, -- foreign key here
28    ColumnName NVARCHAR(255),
29    ColumnValue NVARCHAR(2048),
30    -- you may want to extend this to store string, number, date
31    -- data differently
32    PRIMARY KEY(VendorID, ColumnSetID, ColumnName)
33);
34
35CREATE TABLE dbo.AdditionalDataAccess
36(
37    CorporationID INT, -- foreign key here
38    VendorID INT, -- foreign key here
39    ColumnSetID INT, -- foreign key here
40    HasAccess BIT NOT NULL DEFAULT (1),
41    PRIMARY KEY(CorporationID, VendorID, ColumnSetID)
42);
43
44-- now, you can check for HasAccess in this table
45-- you can also infer from lack of being in this table
46-- whether that means they have access or they don't
47-- have access to a particular column set.
48
49-- ultimately, after you got hte base data from the
50-- standard tables like Vendors, the query would look 
51-- something like this, if presence in the 
52-- AdditionalDataAccess table is required:
53
54DECLARE @CorporationID INT = 1, @VendorID INT = 1;
55
56SELECT
57    ColumnName,
58    ColumnValue
59FROM
60    dbo.AdditionalData AS ad
61WHERE
62    VendorID = @VendorID
63    AND EXISTS
64    (
65        SELECT 1
66            FROM dbo.AdditionalDataAccess
67            WHERE ColumnSetID = ad.ColumnSetID
68            AND CorporationID = @CorporationID
69            AND VendorID = @VendorID
70            AND HasAccess = 1
71    );
72
73-- you'll have to pivot or transform in the client to
74-- see these as columns instead of rows