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


Jonathan Leffler wrote:
> 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.

Cool ;)

>
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

.

Relevant Pages

  • Re: performance questions and help!
    ... The last link provided by Robert Klemme is the Oracle 10g R2 ... you a different execution plan than what was actually used when the ... using bind variables is usually better than specifying ... constants in the SQL statements. ...
    (comp.databases.oracle.server)
  • Re: Does Optimizer use P.A.T to calculate cost..?
    ... hints to the SQL statements to essentially force Oracle to use index ... I want to move away from using the 'old' method of manually specifying ... Some of those SQL statements that I brought over ... likely resulting in multi-pass sorts. ...
    (comp.databases.oracle.server)
  • Re: Does Optimizer use P.A.T to calculate cost..?
    ... Scan through the trace file and look for the wait events ... I rely on NOWORKLOAD system statistics - the default behaviour. ... hints to the SQL statements to essentially force Oracle to use index ... Some of those SQL statements that I brought over ...
    (comp.databases.oracle.server)
  • Re: Informix beats Oracle
    ... This could be interpreted as you finally saying IDS is cheaper than Oracle... ... comparable features if such a beast exists. ... "IBM Informix Dynamic Server, Version 10 IBM Client SDK, Version 2.9" ... family of servers do not support fragmenting by range, ...
    (comp.databases.informix)
  • Re: Does Optimizer use P.A.T to calculate cost..?
    ... Scan through the trace file and look for the wait events ... I rely on NOWORKLOAD system statistics - the default behaviour. ... hints to the SQL statements to essentially force Oracle to use index ... Some of those SQL statements that I brought over ...
    (comp.databases.oracle.server)