Re: Research and EAV models

On Fri, 2009-10-23 at 23:53 +0200, Leif B. Kristensen wrote:
> I've followed this list for quite a long time, and I think that I've
> discovered a pattern that I would like to discuss.
>
> It seems like there are two camps considering EAV models. On the one
> hand, there are researchers who think that EAV is a great way to meet
> their objectives. On the other hand, there are the "business" guys who
> thnk that EAV is crap.
>
> I've seen this pattern often enough and consistently enough that I think
> there may be an underlying difference of objectives concerning the use
> of databases itself that may be responsible for this divergence.
>
> I'm a researcher type, and I've made an EAV model that suits me well in
> my genealogy research. How can you associate an essentially unknown
> number of sundry "events" to a "person" without an EAV model?
>
> It seems to me that data models made for research is a quite different
> animal than data models made for business. In research, we often need to
> register data that may be hard to pin down in exactly the right pigeon
> hole, but never the less need to be recorded. The most sensible way to
> do this, IMO, is frequently to associate the data with some already-
> known or postulated entity. That's where the EAV model comes in really
> handy.

This problem is common in many different areas, not just research.

In most data models there will be parts that are well known and parts
that are changing rapidly. For example, in banking, a customer "account"
has been defined the same way for almost as long as computers have been
around. However, customer characteristics that the bank wishes to track
for fraud detection are newly invented each week.

The way you model data should not be only one or the other way. You
should model your well-known portions using relational models and the
faster changing/dynamically developing aspects using EAV models. You can
put both into an RDBMS, as Karsten shows. I've seen that implemented in
various ways, from including XML blobs to text strings, EAV tables or
something like hstore.

--
Simon Riggs www.2ndQuadrant.com