Re: How to model searchable properties of an entity

>> There are some situations where that process model fails because
the metadata is unknown at design time, the information is not available to the users and so can't be encoded as the database structure. The example I usually quote is the library system. New classifications need to be added and the system has to be able to cope with it. <<

That is a bad example; libraries have been pretty well defined and encoded for at least the last century. If I need a new classification, I have the Dewey Decimal framework to look it up if it already exists, or to get some open slots if it really is brand new.

>> One solution is to train all of the users up to DBA level and give
all of them CREATE and ALTER TABLE permissions. When a user discovers a new attribute they just add a new field to the appropriate table. Having multiple DBAs working on one system is problematic but survivable. <<

Been there; done that. Prostate cancer is also survivable.

>> The entity-attribute-value system is an alternative but I don't
believe it is much better, and I speak as one who has built such systems. You still need every user to have pretty much the same training as a DBA. If you can't ensure that all of your users are of that calibre then the system will rapidly become unworkable. <<

You have to ensure that all the present users are of the highest calibre and then ensure that all the FUTURE users are too! If you can make prediction like that, then play the horses and get out of IT.

>> I sometimes use an e-a-v system as part of the process of designing
a new system, but I haven't yet handed over a finished design with it still in place. <<

If I need a "sand box" database during the design phase, I have found it is easy for me to write DDL on the fly. If I am gathering very raw data, a notebook is usually fine. I don't see EAV as even a good development tool.

>> If you are going to have the metadata evolving as the database is
built then there is no alternative but to have highly trained users with an understanding of information management. There are several different ways of using them but this basic requirement is pretty much unavoidable. <<

I agree. Have you run into the "Agile Database" people yet? They seem to want to let the programmers design the database on the fly and get rid of the DBA completely. Received on Wed Aug 18 2004 - 17:30:00 CEST