1

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.

Improve this question
  • 2
    What you are describing is known as the "Entity Attribute Value" anti-pattern. Essentially you are building a DBMS using SQL. This is pretty hard to query and maintain. Search the web for EAV and you'll see pletty of examples. My approach would be to have a base users table and one table for each customer with the columns they need (1:0..1 relationship). Then join the "satelite" tables to the main tables depending on which customer uses the software. You'll get much better performance especially when you need to do reporting. – a_horse_with_no_name Sep 3 '13 at 11:27
  • Thanks for that. I've just stumbled upon this useful post which talks about dynamic form creation, which is very helpful and seems fairly straightforward. Not sure if you have anything to warn about it's content? stackoverflow.com/questions/792278/… – EvilDr Sep 3 '13 at 12:21
2

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.

Improve this answer
  • Thanks Rex, that's very helpful. We're not actually using EntityFramework; just simple webapps plus SQL stored proc's. I've just read on MSDN actually that SqlVarient might not work for us, because we may need to store nvarchar(max). That's a pain... :-( – EvilDr Sep 3 '13 at 10:53
  • 1
    yes, indeed. we did not use SqlVarient, but a varchar(max) field, which eventually hit a performance bottleneck because of the updatable views (which convert the type from varchar to desired type for each column). Thus I would suggest you to try out the child-table (1 to [0 or 1]) approach. as you are not using entity framework, you could also try the updatable views too (join the main and child table for each type of user, and then have an updatable view for each user type) – Rex Sep 4 '13 at 1:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.