Re: A searchable datastructure for represeting attributes?

From: Jim Harrington <jharrington_at_accessdc.com>
Date: Thu, 28 Feb 2002 17:31:06 -0500
Message-ID: <u7tc12qvkugse5@corp.supernews.com>

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 - 16:31:06 CST