Re: maximum number of columns per table

On Wed, 21 Jul 2004 06:24:51 -0700, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>Tony wrote:
>> That was a joke, I hope? You are not seriously advocating the
>> egregious generic attribute/value table that database "designers" with
>> about 6 months' experience are always so excited about? Please tell
>> me that was a joke!
>
>As serious as a heart attack.
>
>Daniel Morgan

So we're just one footstep from the application independent datamodel:

CREATE TABLE my_tiny_objects
(obj_id NUMBER)

CREATE TABLE date_values
(obj_id NUMBER,
 attribute_name VARCHAR2(30),
 value DATE);

CREATE TABLE num_values
(obj_id NUMBER,
 attribute_name VARCHAR2(30),
 value NUMBER);

CREATE TABLE char_values
(obj_id NUMBER,
 attribute_name VARCHAR2(30),
 value VARCHAR2(4000));

generic, reusable and vendor independent

LOL...ROFL

ok, let's get serious. Storing the attribute name in a separate column requieres space. You get less data in the same number of blocks. To gather your data, you have to visit more blocks, no doubt. We have apps wich requiere tables with 200 columns, each one having a distinct meaning. Converting our horizontal design to a vertical design (been there, done that) blows storage requierements by a factor of 2.5 => Performance degrades significantly. We decided for the faster horizontal approach, paying for that with more coding. The requierement was performance at runtime, not at coding time..

--
Marc Blum
mailto:blumNOSPAM_at_marcblum.de
http://www.marcblum.de