Answered by:
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
Monday, July 23, 2012 3:34 AM
Answers
-
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- Marked as answer by amber zhangEditor Monday, July 30, 2012 5:52 AM
- Edited by Kalman Toth Saturday, October 13, 2012 6:13 PM
Monday, July 23, 2012 7:29 AM -
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 HoodThis 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.
- Proposed as answer by Shulei ChenEditor Wednesday, July 25, 2012 9:18 AM
- Marked as answer by amber zhangEditor Monday, July 30, 2012 5:52 AM
Monday, July 23, 2012 7:59 PM
All replies
-
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- Marked as answer by amber zhangEditor Monday, July 30, 2012 5:52 AM
- Edited by Kalman Toth Saturday, October 13, 2012 6:13 PM
Monday, July 23, 2012 7:29 AM -
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
Monday, July 23, 2012 9:10 AM -
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
- Edited by Kalman Toth Monday, July 23, 2012 4:08 PM
- Proposed as answer by Shulei ChenEditor Wednesday, July 25, 2012 9:18 AM
Monday, July 23, 2012 4:06 PM -
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 HoodThis 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.
- Proposed as answer by Shulei ChenEditor Wednesday, July 25, 2012 9:18 AM
- Marked as answer by amber zhangEditor Monday, July 30, 2012 5:52 AM
Monday, July 23, 2012 7:59 PM