Extensible Schema

[kettch]

I'm looking to do a project that will involve the user being able to add and remove fields on the fly.

One of my ideas involved having just basic entity table (for example: Contact), a Fields table (with the field name, datatype, etc...), and a link table that essentially has a link to the entity, a link the field, and a column with the actual data in it.

Can anybody offer any advice on doing this sort of thing? Stuff like, am I doing it wrong, am I nuts, etc.
I'm particularly interesting in the datatype of the actual column that is going to store the data, what should it be? Obviously a lot of conversions and checking is going to happen in code, but that's not really a topic for the dbms forum, is it? If anybody can offer any guidance on this then that would be greatly appreciated.

 

[rac2]

Maybe you are nuts, maybe you are brilliant.

I have seen a couple of commercial examples using that kind of structure. It allows the user ( specifically a person in an administrator role in the company that purchases the application from a software vendor) to add new data elements to the basic application.

In one, the definition of the data element would be stored in a row in a Data Specification Table. So the system might come with 50 DST rows, and the "user" might add any more that were needed. The definition of the field would include all of the information needed to accept and display data. The data itself was stored in a kind of name/value pair, actually a name/value/date triplet so that the age of the item of data was also known, multiple occurrances of values for the same data about a particular entity were also possible. Two kinds of entities were possible, staff and customers, probably the DST kept track of which entity the data element applied to. Although that was not a relational database exactly, the storage might be a single table with a gazillion rows, each row being a name/value pair. The name is the link to the structural information in the Data Specification Table. And of course a key linking the value to one of the entity instances.

The other example is realized in a relational database, MS SQL Server. The structure is a little different. It consists of a two tables for defining the structure of sets of data elements, Details and DetailItems. These tables contain information about sets of element and elements in the sets and how to display them. The values were stored in another pair of tables with foreign keys to the other two which enable display, update, and delete. These were Object and ObjectValue tables. The ObjectValue table had a foreign key to the Object table. A row in the Object table plus the related rows in the ObjectValue table represent a particular set of values of some kind (Details and DetailItems) about an instance of the entity. The data elements in this system pertained to only one entity. A foreign key to the entity table was included in the Object table.

As to datatype, the first system, I dont recall. The second had two columns for values, one for strings, VARCHAR, and one for numbers, DECIMAL. Note that XML data structures store all data as strings, the strings have various pre-defined formats which may be massaged by the XML data consumer. So maybe you could get away with one column of VARCHAR.

I myself am developing an application modeled on the second one, with Categories, Details, DetailItems, Facts, and Factoids. I substituted Facts for Objects for idiosyncratic, aesthetic reasons. I found it an interesting puzzle fitting these four things together. I have the luxury of time to experiment. Once you get the idea of a data element consising of elaborated name-like and simple value-side parts, it seems to work well. I have a simple but infinitely expandable display worked out, the CRUD functions are working, and today I should be working on the admin, ie create new element function, if I werent writing here.

In conclusion, if we are crazy, we are not alone. Time will tell whether we were brave or foolish to try.

 

[mrdenny]

I built a system inventory app like this a while back. It worked faily well. I used the two table approach. One had the deffination (text field, check box, number field) and the name of the field. The other had the actual data. I wasn't keeping historical data so there wasn't a need for date/time stamping.

It was hard for some people to wrap there head around (especially the reporting), but the system worked well.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com