Re: EAV (Re: Object-relational impedence)
Date: Fri, 28 Mar 2008 16:20:01 -0700 (PDT)
Message-ID: <1f649255-eba1-4e82-82b1-99228e761fa5_at_c19g2000prf.googlegroups.com>
Brian Selzer wrote:
> "topmind" <topmind_at_technologist.com> wrote in message
> news:79cae24d-bca7-47e5-94f6-7bb5bfa508f1_at_s8g2000prg.googlegroups.com...
> >
> >
> > 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?
> >
>
> Since when do widgets create their own tables?
I thought that was *your* suggestion. May I suggest you describe a sample scenario to reduce unstated assumptions between us.
>> > have personal preferences that differ.
> > And, I find gazillion tables hard to navigate when looking for tables
> > or debugging. I never liked table-happy designs, but individuals may
> >
>
> For me, it's a lot easier to wade through a gazillion tables than to try to
> decipher the content in an EAV table.
That's you, not me. In this case I prefer EAV.
> 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 don't see why that would be a common scenario for GUI's. I cannot even think of a single realistic case right now. I am not saying it will never happen, but its not common enough to tilt the decision unless its close.
Plus, lots of similar tables often results in having to do UNION queries, which are ugly and slow. With EAV, one could get/dump the GUI attributes in a single query. With your myriad table suggestion, one would have to first take an inventory of all custom-widget tables.
>> > practical either. With what exist, EAV looks like the best bet still.
> > 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
> >
>
> I can't really comment on something that doesn't exist.
What is missing from the draft specs? Anyhow, let's table (pun) Dynamic Relational for now, and focus on how *current* RDBMS act. We have enough on our table (pun) that discussing DR at this point would just bloat up the thread.
>> > reader.)
> > (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
> >
>
> ???
Nevermind. Just implementation details we can worry about later.
>> > Please clarify. I'm missing something.
> >> 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?
> >
>
> You wouldn't need a separate XY-having table, since that is just the join of
> the X-having and Y-having tables.
I am not understanding your setup here. Again, it seems to be scenario time. We need to see what the common scenarios are and optimize our decision for those (without preventing known UNcommon needs).
-T- Received on Sat Mar 29 2008 - 00:20:01 CET