Extensible Database Schema

I'm working on a project that is going to need some parts of the database schema to be extensible, as in being able to add/remove fields on the fly. I've kicked around some ideas, but I wanted to see if anybody here had any experience with this sort of thing. Or some references would be great too.

Obviously this has been done before in CRM and other products, but I don't really need that level of complexity.

Thanks

kettch wrote:



I'm working on a project that is going to need some parts of the database schema to be extensible, as in being able to add/remove fields on the fly. I've kicked around some ideas, but I wanted to see if anybody here had any experience with this sort of thing. Or some references would be great too.

Obviously this has been done before in CRM and other products, but I don't really need that level of complexity.

Thanks

I've done a couple of things along these lines:

1. Add a column to each of your tables called AdditionalData or XmlData.  You can define XML schemas for your different database entities and store additional information there.  If you're using SQL 2005, you can make it an xml typed column and even query using XPath, (or so I'm told.)
2. I've also created a product database where each Product row could link to one or more ProductAttribute rows.  Each ProductAttribute had a AttributeName and an AttributeValue.  This allowed me to add arbitrary attributes to products, and I was able to write pretty flexible queries to search for products on any number of attributes.

You're definitely going to want to consider the boundaries of your extensability, because otherwise, you'll have a DB schema that is completely extensible  and completely useless [C] in the real world.

Let us know what you come up with! 

Your second idea is pretty much what initially came to mind, it definitely makes for a long table. With the proper structure and constraints it could work well. I'll think about this for a while longer before I come up with anything final.

I'll try to get my ideas solidified to post later on.

I've been digging around the Team Foundation Server database recently, which looks pretty extensable.
If you have access to it and want to see a working example, I'd recommend spelunking around it.

Herbie

A couple of weeks ago I was doing some research on how to link microsoft CRM to one of the ERP systems we use. I came across some pretty nifty extensionpoints in the database.

Microsoft uses [Entity]Base as the base table, this table can't be changed (It can, but you break stuff) they added a table [Entity]Extension which is linked to the base table. This allows the user to add new fields to an entity.

It's pretty much the same as the others here mentioned. I personally think this is the best way to go.