I want it customizable, how do I achieve this?

I have spent some time recently working on the product design which would make it customizable, to the point that users will be allowed to add new entity attributes, or even define new entity types.

Good example (thanks Kirill for this idea) would be TFS . Default configuration allows you to create work items of types Bug, Issue, Task etc.:

Each type has its own set of fields, like Title or State:

But you are free to add your own fields, or even define new item types

(see http://msdn.microsoft.com/en-us/library/ms400654(v=vs.100).aspx)

One of the ways to achieve this degree of extensibility is to use a Generic Attribute Table, which is well-recognized antipattern. In such design all field values are stored in a single table with fields like ObjectType, ObjectID, FieldID, FieldValue.

Of course, you lose most of the good stuff the relation database provides, including referential integrity, mandatory fields, and SQL data types.

So how did guys from TFS team solve this task?

Let’s first look how the default work items are stored in the database. Quick examination of tables and fields leads to dbo.WorkItemTypes table.

The work items data itself stored in dbo.WorkItemsAre table:

And here’s something interesting: fields with those weird names like Fld10013. We can guess that these fields, are, in fact, custom fields.

Let's follow the steps from MSDN and create a new work item type. 

Export the bug definition:

Modify it in notepad:

Import it back:

Now if I go to VS, I can create the work item of my custom type:

And fill my custom field:

Now let’s look into the database.

As expected, we discover that the new record appears for the new custom item type:

What is the most interesting is that we see a new field added to dbo.WorkItemsAre table:

We can see that it has the value that we provided.

Field metadata can be found in dbo.Fields table:

So we can see that TFS uses Single-Table Inheritance (http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html) and allows you to add fields for the custom attributes.