Extend database for multi-tenant application

Question

We're rebuilding a multi-tenant asp.net web app and I'm having trouble nailing down the most appropriate database structure. I've read loads of questions/answers on SO and the Microsoft multi-tenancy architecture article, but can't seem to find the info I'm looking for.

The application has 2 types of end users:

• A vendor who inputs their company's information into the database (things like address, contact info, insurance amounts, etc.)
• A corporation hiring the vendors. The corporation user logs in and views the data the vendor has entered. Corporation users can search vendors, run reports, etc.

Issue:

• Each corporation that uses the system wants different information from the vendors. They all want basic info like address and contact info, but then some will want an abritrary number of additional fields (specific insurance info or something) that apply only to that corp.

My inclination is to use Name-Value pairs for the database fields so we can extend the database as needed for each corporation. My questions:

• Does this seem like the best approach given the info above and the fact that name-value will significantly complicate querying/filtering vendors. The other option I'm considering is creating a separate "additional fields" table for each corporation that has the fields required by that particular corp.

Input/answers greatly appreciated.

Answer 1

I've done EAV solutions in the past, and they work quite well. I think this could work for your situation if I understand it right (that the vendor-corporation relationship is many-to-many / all-to-many). I've written about the pros and cons of EAV here:

• What is so bad about EAV, anyway?

Whether this is appropriate for you will depend on a couple of things. Are the "additional fields" you mention a relatively fixed set, or are they added on demand? If they are a relatively fixed set, then you could just have an access (not Microsoft Access) table that tells whether each set of columns is relevant for that vendor-corp pair.

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

Hope this is useful and makes sense.