Re: How to model searchable properties of an entity

In message <18c7b3c2.0408131758.65f91710_at_posting.google.com>, --CELKO-- <jcelko212_at_earthlink.net> writes
>>> Let us say I have Part# and it has very large number of flexible
>attributes defined by the user, such as color, width, height, etc...
><<
>
>Fire the DBA immediately; users NEVER get to define the database and
>this bum is not doing his job.

It's normal for the attributes of the entity (the metadata) to be known at design time, and the DBA is required to extract that knowledge from the users and code it into the database.

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.

This isn't a desirable situation and it's best avoided if possible, but if you get handed a lemon like this then knowing a recipe for lemonade is useful.

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.

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. 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 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.

If your budget won't stretch that far then try to fund a one-way ticket to Acapulco.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.