Re: EAV (Re: Object-relational impedence)
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
Perhaps some testing is in order.
> There's another suggestion that nobody's mentioned here that I know
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
> 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.
>
> 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.
>
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.