The original approach almost describes a property bag; you'd have the base table, then a table with a list of property names and types (and an ID) and a value table containing the member ID, the property ID (compound foreign key) and the actual value.
Here's a CodeProject article on PropertyBag
implemented in C#, but not as a database schema.
I found an article on SSWUG.org for a db implementation, as you described, kind sir, but I don't subscribe and cannot retrieve the article. Do you have a reference ICanDownload for property bag as a db schema?
No; it's just one of the questions I ask at interviews
Consider the following awful ascii diagram;
So pretty much like a base class would be. The problem arises in, for example, subclasses, each of which may add more properties, not good as you don't want to add each potential field to the table, that's not maintainable in a flexible manner.
So you create a Properties table;
where type is probably the underlaying framework type, so you can validate
Then you need to connect an optional or extended property to the Item so you do
So creating a new extended property is a matter of creating a new Property in the Property table, and then creating an ItemProperty row for each instance of that property.
You can perform validation at the business layer, or in the stored procedure that attaches a property to the item. You can write a specific stored proc to create properties for a new subclass, if you're going that route, otherwise you will need to add/edit
them one by one.
Then in the method that returns your property values, extend it to return a second recordset with all the properties for your object and pump them into your property bag implementation code side.
I've used this to great effect on a site which has around 2 million hits a day. You would probably want to cache the Properties table code side to make it more scalable.