Re: EAV (Re: Object-relational impedence)

"topmind" <topmind_at_technologist.com> wrote in message news: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.
>

Widgets don't necessarily need to know how to serialize themselves. They only need to know how to provide a snapshot of themselves for serialization. Some other part of the system may be responsible for committing that snapshot to the database.

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

I don't see why you would need to do union queries. I think you're stuck on the idea that each widget type has its own table. That's not at all what I am suggesting. There is a table for attributes that are common to all possible widgets, and a table for each set of attributes that are common not to all possible widgets, but rather to a subset of all possible widgets. So for example, suppose that widget A has attributes T, U, V, and W, and widget B has attributes T, U, V, X, and Y, and widget C has attributes T, U, V, Y and Z, you wouldn't necessarily have a table for each type of widget. Assuming that T, U, and V are common to all widgets, you would have one table, the common table, with attributes T, U, and V. Let's also assume that values for T uniquely identify a widget, meaning that T would be the primary key of the common table. Then you would have a table with T and W, a table with T and X, one with T and Y, and one with T and Z, each referencing the common table. Widget A would have a row in the common table and one in the table with T and W; widget B would have a row in the common table, one in the table with T and X and one in the table with T and Y; widget C would have a row in the common table, one in the table with T and Y and one in the table with T and Z. When a new widget, D, that has attributes T, U, V, X and P needs to be recorded, a new table with T and P would need to be created, and then widget D would have a row in the common table, one in the table with T and X and one in the new table with T and P.

>>
>> > 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.
>
> 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.
>
>>
>> > (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.)
>> >
>>
>> ???
>
> Nevermind. Just implementation details we can worry about later.
>
>>
>> >> 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.
>
> 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 - 05:47:01 CET