Re: EAV (Re: Object-relational impedence)

Brian Selzer wrote:
> "topmind" <topmind_at_technologist.com> wrote in message
> news:a8855c5d-1e3f-46ef-a99a-5c09b94bfba5_at_u10g2000prn.googlegroups.com...
> > (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?

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.

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

-T- Received on Fri Mar 28 2008 - 18:20:03 CET