Re: EAV (Re: Object-relational impedence)

From: Brian Selzer <>
Date: Fri, 28 Mar 2008 19:25:12 GMT
Message-ID: <sCbHj.20553$>

"topmind" <> wrote in message
> Brian Selzer wrote:
>> "topmind" <> wrote in message
>> > (addendum)
>> >
>> > For an example of the need for flexible columns/attributes, consider a
>> > relational-based GUI system where widget state and info are tracked in
>> > some kind of RDBMS. We want it to be able to add new widgets from
>> > different vendors without each widget needing its own table(s) (unless
>> > its a really special need).
>> >
>> > There seems to be a need for either a EAV table (row-based dynamism)
>> > or Dynamic Relational (column-based dynamism) of some kind so that
>> > attributes that are not known up-front can be tracked.
>> >
>> > If you suggest otherwise, please present your design.
>> >
>> Well it's really simple. You have a single table with attributes that
>> are
>> common to all possible widgets, and when a set of attributes that are not
>> known up front needs to be tracked, a new table or set of tables is
>> created
>> to house the set of attributes that references the table with the common
>> attributes. It's really very simple and doesn't require a special
>> dynamism
>> (Is that the correct use of that word?) mechanism.
> Having different vendor widgets being able to create their own little
> table is not very practical. There are versioning issues, for one.
> What if a widget creates a table that does not work with the current
> version or brand of our GUI RDBMS?

Since when do widgets create their own tables?

> And, I find gazillion tables hard to navigate when looking for tables
> or debugging. I never liked table-happy designs, but individuals may
> have personal preferences that differ.

For me, it's a lot easier to wade through a gazillion tables than to try to decipher the content in an EAV table. The 'table-happy' design permits the definition of relationships between attributes. That is a whole lot more difficult when everything is stuffed in an EAV table. For example, suppose that every widget that has attribute Y also has attribute Z. Try enforcing that rule with everything stuffed in an EAV table. With the 'table-happy' design, all you need is to specify that attributes Y and Z are in the same table.

> I see nothing wrong with the concept of Dynamic Relational; other than
> the fact it doesn't fully exist so far (details details :-). It would
> rid the need for a common table and separate widget-specific "custom"
> table(s). But what does exist does not make mass table creation
> practical either. With what exist, EAV looks like the best bet still.

I can't really comment on something that doesn't exist.

> (I'd have an "overflow" marker for cases when the column width exceeds
> the EAV value size, usually 255 in existing engines. The overflow
> marker would reference an open-ended text field in an "overflow"
> table. It usually is not practical to have an open-ended value column
> in most EAV's, but it depends on the DB vendor. Whether the reference
> ID would be in the EAV or the overflow table, I'll leave to the
> reader.)


>> In fact, this design is
>> also very economical, since each referencing table only contains rows for
>> those widgets that possess that attribute (or set of attributes), and
>> there
>> is no need to record the attribute names over and over again.
> I don't think that's a real problem. GUI's are not going to have a
> million+ widget instances. If this was 1971 when you had to sell a car
> to buy 1 meg of disk-space, then it would be an issue. I'm sure the
> overhead of gazillion tables is not resource-friendly either.
>> Note that
>> each widget does not necessarily need its own table: there could be
>> widgets
>> that possess attribute X, those that possess attribute Y, those that
>> possess
>> attributes X and Y, etc. The widgets that possess attributes X and Y
>> would
>> have rows in the table housing X attributes and the table housing Y
>> attributes as well as the table housing the common attributes.
> Why would it use both the X-having table and the Y-having table?
> Please clarify. I'm missing something.

You wouldn't need a separate XY-having table, since that is just the join of the X-having and Y-having tables.

> -T-
Received on Fri Mar 28 2008 - 20:25:12 CET

Original text of this message