Re: EAV (Re: Object-relational impedence)

From: Brian Selzer <brian_at_selzer-software.com>
Date: Mon, 31 Mar 2008 09:20:21 GMT
Message-ID: <p12Ij.23069$0o7.8228_at_newssvr13.news.prodigy.net>


"topmind" <topmind_at_technologist.com> wrote in message news:a742d466-e6a9-4d13-a1ba-953567fcb3dc_at_u10g2000prn.googlegroups.com... [big snip]

> It's a gut feeling for everyone here because it's never been tried in
> practice. We cannot really know with near certainty all the possible
> usage scenarios (query patterns) and which are more frequent. It's an
> educated WAG at best.
>

Perhaps some testing is in order.

> There's another suggestion that nobody's mentioned here that I know
> of: Add each new column to the Widgets table as needed, creating a
> "sparse" table. If we are going to allow add/change to schemas, then
> why not just add the new frippen column to the Widgets table and be
> done with it? Sparse tables are not really space hogs on most modern
> RDBMS like they were in the past. The overhead for unused (null)
> columns is small.
>

It's not about space. Separate tables can be indexed separately, permitting the selection of better query plans. A lot of nulls, such as you would find in a sparse table, skews the statistics for columns, making some indexes that would otherwise appear optimal less attractive, leading to poor performance.

The idea of a sparse table is ugly because it involves nulls. Nulls--especially those that indicate 'there shouldn't be a value here'--should be avoided whenever possible. If you're interested, there have been many discussions on cdt regarding nulls. If you can wade through the flames, you might even find some useful information.

Data has an inherent structure. Stuffing everything into one table, whether it be an EAV table or a sparse table imposes an alien structure on the data, introducing redundancy and complexity and usually reducing performance. A solution that has one table per widget type also imposes a structure on the data, one that inevitably introduces redundancy. The 'table-happy' design that I suggested earlier does not introduce redundancy and does not require nulls. Received on Mon Mar 31 2008 - 11:20:21 CEST

Original text of this message