views:

29

answers:

2

Maybe this is simpler and more straightforward then what I'm thinking but I can't seem to find this concept on google anywhere. The concept is this:

You have a table in a database and the table has a specified number of columns. However, it has been asked of me by previous clients that there also be a set of dynamic user defined columns that can be added on the fly.

What is this concept called and is it considered a design pattern?

+1  A: 

You're probably talking about name-value pairs.

Like this:

UserProfile
-----------
UserID  PropertyName  PropertyValue

But it doesn't mean you add columns to a table dynamically. It's not done this way.

Officially it is known as the Entity-attribute-value model.

Developer Art
A: 

I suspect clients require to have ability to create entities, stored in database, with custom fields.

There are several approaches:

You can create a table with common entity fields (id, dateCreated, dateModified, isVisible, typeId ...) and store additional specific fields in xml-field (one table for all entity types).

If Xml data type look overegenireed to store data you can use nvarchar where attributes stored in JSON notation. Binary format can also be used if size does matter :)

Or you can create related table to store specific entity attributes. This approach is implemented in asp.net profile default provider: table for users and table for user profile with pairs [userId, profile-attribute-name, profile-attribute-value

What to choose depends on other requirements.

Andrew Florko