Re: Modelling objects with variable number of properties in an RDBMS

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 01 Nov 2005 04:59:12 GMT
Message-ID: <ACC9f.4437$yX2.1761_at_newsread2.news.pas.earthlink.net>

vc wrote:
> Roy Hann wrote:
> ....
>

>>Actually no, let's not.  What you have re-invented here is the tired old
>>entity-attribute-value (EAV) design (much beloved of medical research wonks
>>for some reason).

>
>
> Often they do that (EAV) not because they do not know any better but in
> order to overcome a specific database physical limitations. Their
> entities very often contain thousands of attributes (molecular
> biology/pharmaceutical research/drug discovery), and for example
> Oracle supports maximum 1000 columns.

I have a set of SQL statements suitable for IBM Informix Dynamic Server to create a table with 32767 columns - all CHAR(1) since the maximum row size (ignoring blobs) is 32767. Said table wreaks all sorts of havoc on utilities because a single SQL statement only goes up to 64K of text; even loading it is entirely non-trivial. However, 1000 is certainly not the only limit out there - there are higher limits.

> I believe that only Oracle and DB2 have support for user defined types
> which would allow to solve the multi-thousand attribute problem
> differently.

And IBM Informix Dynamic Server - again.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Received on Tue Nov 01 2005 - 05:59:12 CET