What is the best approach to manage User Defined Fields?

Question

JHello,

      I am looking for a best approach to manage custom fields inside my next project. I have find the practice of keeping few fields inside the table with name as customfield1,customfield2 etc then later map these fields at UI level with user defined captions. But in this case my available custom filed options will be static. Is there any other approach I can manage this situation?

    In addition to above issue I am also looking for a solution useing dynemic types of FW 4 with EF. Is it possible with these two technologies I can populate some fields at runtime for my EF objects?

I am using SQL Server 2008 R2 as a database engine.

     Any suggestions, guidelines, refferences will be apprecited.

Nilkanth S. Desai

Answer 1

There is no need to keep custom field "placeholders" in a table. You can add new columns with ALTER TABLE to empty or populated tables. 

The following blog post has ALTER TABLE examples:

http://www.sqlusa.com/bestpractices2005/addrownumbertotable/

You may want to elaborate on your EF question.

You can create tables dynamically using dynamic SQL:

http://www.sqlusa.com/bestpractices/dynamicsql/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

Hello,

      Thanks for your reply. I could not workout how to implement u r suggestion. Please note that I have to provide my end users a facility in which they can add custom columns which may not be identified at design time and which is not of type rowId or uniqueidentity which is used for internal but for their personnel use like string for remark, date for delivary date, money for tax etc.

    So please explain with both the issues I mentioned in little detail.

Thanks for your reply,

Nilkanth S. Desai

Answer 3

Probably the best solution is a UserCustomColumn table.

CREATE TABLE UserCustomColumn (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	UserID INT NOT NULL REFERENCES User (UserID),
	SchemaName sysname not null,
	TableName sysname not null,
	ColumnName sysname not null,
	ColumnType sysname not null,
	ColumnValue nvarchar(max),
	ModifiedDate datatime default (CURRENT_TIMESTAMP));

The above solution gives the user the illusion of adding columns to a table with virtually total flexibility.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING

Answer 4

I would largely agree with Kalman, with a few minor changes.

Adding a column to the table as the user needs can be the easiest way, if the number of columns is reasonable (and not terribly large)..

So if you have a table like the following:

CREATE TABLE Customer (        CustomerNumber  char(10) PRIMARY KEY,        CustomerAttributes various )

Then just provide an interface that can add a new column:

ALTER TABLE Customer  ADD NewAttribute numeric(10,2) SPARSE NULL;

You get all of the benefits of a column in a database, usage is easy, etc. It is a change to the schema, so it is not the simplistic answer, but it certainly could be the best performer as you can index the columns, put constraints, etc.

For the easier to build but harder to use solution, I would probably implement a user defined table per table you need to extend and not do one per database as that will be much harder to manage over time.  I also tend to have one column for value, either a nvarchar type or a sql_variant. Most values can be stored in a varchar, and with a little enginuity you can create a datatype tester in your stored procedure or trigger to make sure the data meets the desired type. So if you had a student table with a surrogate primary key of StudentId, you could create StudentExtentionAttributeDefinition (Key: Attribute)

Attribute          Datatype
---------------- -------------------------
Seat Assign     char(2)
PassNumber    integer
PlayCharacter  nvarchar(100)

And then the following 3 column table with Entity Key, Attribute Key, and Value

StudentId   Attribute          Value
-------------  ------------------- -------------
1                Seat Assign      A4
2                Seat Assign      B2
1                PassNumber    33
1                PlayCharacter  Red Riding Hood

This is a lot easier for the UI programmer, but when you have to do a query on this data, it is not a simple: SELECT columnList FROM Student, like our Customer example would be

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.