Re: A searchable datastructure for represeting attributes?

Jim,

Thanks for your reply. It was very useful. The reason you mention about customizing w/o changing the schema is similar to what I am facing. Our application is closer to an app server in nature (not actually an appserver) then to a consulting style solution for a specific enterprise. Customization is the reason attibute schemas have to be adjustable on the fly since customers (and suprisingly their DBAs) are not happy when you change the schema (they think it's some major change in the software).

Dre

Jim Harrington wrote:

> Dre,
>
> As I found out when I first suggested something like this to a DBA, this is
> a religious thing.
>
> In our case, there were some attributes that we always expected to be
> associated (with values) with the entity and some attributes that were not.
> Those attributes that would always be filled in, we put as columns on the
> entity table. Those that were optionally filled in, we viewed as a sparse
> matrix, which would naturally be modeled in a manner similar to your
> 'attributes' schema. We did a couple of things differently to address the
> data type and integrity concerns described in previous posts. I'll try to
> schetch out what we did below:
>
> ENTITY
> -- all usually filled in attributes
>
> ATTRIBUTE_TYPE
> NAME STRING
> DATATYPE NUMBER -- (constants
> indicating: NUMBER|DATE|STRING)
> UNIT_OF_MEASURE_ID NUMBER -- (FK to unit table)
> DOMAIN_ID NUMBER -- (FK to table defining
> value ranges)
>
> ATTRIBUTE_VALUE
> ENTITY_ID NUMBER
> ATTRIBUTE_TYPE_ID NUMBER
> NUMBER_VAL NUMBER
> DATE_VAL NUMBER
> STRING_VAL NUMBER
>
> We found that we could enforce constraints via after insert and update
> triggers on ATTRIBUTE_VAUE and that we had the benefit of datatypes. It is
> more expensive to query based on attributes that are off the entity table as
> was mentioned, but that was a cost we decided to pay to gain flexibility.
> In our case, the above schema allowed us to add attributes to the definition
> of an entity when the application was customized for a particular
> installation without having to alter the schema.
>
> Performance in our case was very much acceptable for the >100,000 entities
> we were dealing with at the time. More data could easily swamp this design
> in a given database environment, but if you can scale the database
> environment as the amount of data grows, then it's reasonable to assume that
> acceptable performance can be maintained.
>
> Hope this helps,
>
> Jim Harrington
Received on Thu Feb 28 2002 - 17:28:47 CST