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:
- Create a table called
[tbl_ColumnNames]
that contains each unique column name and data type - Remove all non-generic columns from
[tbl_Users]
move them to[tbl_ColumnNames]
- Create a many-to-many table
[tbl_CustomersColumns]
where we assign columns to any respective Customers - 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.