Custom Fields in .Net and SQL Server

Question

We have a requirement on our project for custom fields. We have some standard fields on the table and each customer wants to be able to add their own custom fields. At the moment I am not interested in how this will work in the UI, but I want to know what the options are for the back end storage and retrieval of the data. The last time I did something like this was about 10 years ago in VB6 so I would be interested to know what the options are for this problem in today's .Net world.

The project is using SQL server for the backend, linq-to-sql for the ORM and a C# asp.net front end.

What are my options for this?

Thanks

Answer 1

There are four main options here:

• actually change the schema (DDL) at runtime - however, pretty much no ORM will like that, and generally has security problems as your "app" account shouldn't normally be redefining the database; it does, however, avoid the "inner platform" effect inherent in the next two
• use a key-value store as rows, i.e. a Customer table might have a CustomerValues table with pairs like "dfeeNumber"=12345 (one row per custom key/value pair) - but a pain to work with (instead of a "get", this is a "get" and a "list" per entity)
• use a single hunk of data (xml, json, etc) in a CustomFields single cell - again, not ideal to work with, but it easier to store atomically with the main record (downside: forces you to load all the custom fields to read a single one)
• use a document database (no schema at all) - but then: no ORM

I've used all 4 at different points. All 4 can work. YMMV.

Answer 2

I have a similar situation on the project I'm working on now.

1. Forget about linq-to-sql when you are having a flexible database schema. There is no way to update the linq-to-sql models on the fly when the DB schema changes.

2. Solutions:

   • Keep an extra table with the table name the values belong to , column name , value etc
   • Totally dynamically change your table schema each time they add a field.
   • Use a NOSQL solution like mongoDB or the Azure Table Storage. A NOSQL solution doesn't require a schema and can be changed on the fly.

This is a handy link 2 read:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056