How to handle programmatic addition of fields?

Question

Yesterday, I asked this question regarding best practices for a simple information retrieval system I am beginning to work on.

Today, my customer asked me if it is possible to allow them to add fields to the primary entity at a later date using the administration interface. That is, the application allows you to search across one database table (let us call it Entities), which may have various associations to other objects, but the focus is on the Entities.

Now, of course, the sky's the limit, and this is possible.

My initial thought with this problem is to create two new tables:

AdditionalFields (FieldID PK, FieldName, IsShownInSearchResults, and other metadata) This is the table that would have its rows created by the admin interface. For instance, if the customer later decides they want to add the ability to track, say, the eye color an Entity, they would create an AdditionalField with a FieldName of "Eye Color".

AdditionalFieldData (FieldDataID PK, EntityID FK, FieldID FK, FieldValue) This is the table relating the Entities to the their respective values for any additional fields added by the customer. For instance, with our eye color example, if we have two Entities (EntityID 3 and 4) at the time the field was added and the new AdditionalField has a FieldID of 1, it is valid to have no eye color data associated with the entities. The customer could then choose, to add the eye color "Blue" to Entity 3, and we'd insert a row to AdditionalFieldData like this: (autogenerated PK, 3, 1, "Blue").

Since I want to easily search across these, I will simply require that all additional fields have string values.

Following the approach I posted in the previous question, I'd be using Linq-To-SQL with a query similar to the following for information retrieval when a user performs a search (of course, the logic for filtering will be more complex in practice):

var results = from s in db.Stuff
          where (s.Name.Contains(textFilter) ||
                 s.Title.Contains(textFilter))
          select s;

I would modify the query to look something like this:

var results = from s in db.Stuff
              where (...existing filter logic...) ||
                    s.AdditionalFieldData.Any(afd => afd.FieldName.Contains(textFilter))
              select s;

So my question is: Is this reasonable? Is there a better solution that I missed? Are there any implications of this approach that I should be aware of?

(Please forgive my silly identifiers, its early for me :)

Answer 1

I'm no LINQ expert, but from a database design point of view what you suggest seems just fine to me.

An alternative which I've seen used a few times is to have a whole bunch of 'spare' fields defined on the Entities table, which the users can enable/disable through the admin interface. You gain some database performance this way (due to the smaller number of joins), but you have to choose up front how many of these spare fields you want, and you end up with large, mostly empty tables for most of the time.

Oh, and you can safely assume that if you choose to add N spare fields, your users will want N+1 ;-)