Imitate 'dynamic' columns in SQL Server

Question

We have a central SQL Server 2012 database. There is a single table [tbl_Users] that stores all kinds of information pertinent to an employee, for example,

• UserID
• CustomerID
• FirstName
• Surname
• EmployeeID

All users are stored in the same table regardless of Customer, and segregated using CustomerID.

Some customers are now asking for new columns to be added, which are only relevant to their particular business. In the past, we've just added the columns into [tbl_Users], but now this table is becoming untidy with too many columns, and for the most part, many row values end up being NULL because of their small relevance to others.

Can anyone offer any suggestions on how to allow us imitate this type of design dynamically (if it is possible). I've considered the following approach:

1. Create a table called [tbl_ColumnNames] that contains each unique column name and data type
2. Remove all non-generic columns from [tbl_Users] move them to [tbl_ColumnNames]
3. Create a many-to-many table [tbl_CustomersColumns] where we assign columns to any respective Customers
4. Create a table [tbl_CustomerColumnData] to hold the data moved from [tbl_Users], possibly with SqlVarient type for the actual data field

Is this a decent approach? Our ASP.NET application would then dynamically create the forms to allow data population. Has anyone used SqlVarient types for this kind of thing? Or, is there a better way altogether?

Many thanks.

Answer 1

I used your approach two years ago for a similar problem, it works fairly well, except a bit pain with linq to sql (or entity framework), plus WinForm:

We created a few updatable views for each type of entity (or customer as per your sample) but linq to sql could not cope well with updatable views, or probably it's not the case now.

Now we used a different approach:

In the main table, we will have a column named Descriminator (something like UserType),

then for each type of entity, we will have its delegated table (extended from the main table), which uses same PrimaryKey as the main table - so of course, a foreign constraint refer back to the main table (i.e. Primary Key and foreign key on the same field), and the delegated table will have the specific columns related to that user type.

it works well too, and with this, we can take the advantage of entityframework's inheritance feature.

that's just my experience, so my suggestion is to write some small test apps to test out your approach(es) and evaluate. then get the best that has good performance and also suits your framework for UI.