Re: Data storage question

Nandakumar wrote:
>
> Absolutely i agree with you! More parameters might be required in
> future. But what is the solution?. How would you cater for the
> increasing columns? Do you suggest a relationalized hierarchical
> structure? But this way, number of records will be enormous! Joins on
> huge tables will worsen the performance.
>
> In article <7prv0f$iqq$1_at_autumn.news.rcn.net>,
> "Jerry Gitomer" <jgitomer_at_hbsrx.com> wrote:
> > Hi,
> >
> > From a relational point of view you have a bad design. In
> > essence you are storing an array in each row of the table. True,
> > when using Oracle, it won't waste space, but what happens the day
> > one item requires 11 attributes? (As improbable as that sounds
> > to you today the probability of it happening is very close to 1!)
> >
> > regards
> > Jerry Gitomer
> >
> > Nandakumar wrote in message <7pkl9o$hfp$1_at_nnrp1.deja.com>...
> > >Hi,
> > >
> > >I need to store the attributes of an item in a table. The
> > attributes are
> > >not pre-defined, but there is a maximum limit to the number of
> > >attributes, say 10. Initially there will only be "2" attributes
> > that
> > >would be stored in database. Unknown attributes are stored as
> > NULLs.
> > >
> > >So i decide to go for a table with 10 varchar2 fields for the
> > >attributes.
> > >
> > >Say, data are stored in this fashion in the database and the
> > number of
> > >records has reached 5(or more) million.
> > >
> > >Now, if i find out that there won't be any need to store more
> > than "2"
> > >attributes in the table how bad is my design of the table. How
> > much
> > >storage space would i have saved had i designed the table with
> > just "2"
> > >varchar2 fields?
> > >
> > >mmm! i hear you all say this is an one line question!!
> > >Thanks
> > >--
> > >Nandakumar
> > >Systems Analyst
> > >New York
> > >(N.Kumar_at_rocketmail.com)
> > >
> > >
> > >Sent via Deja.com http://www.deja.com/
> > >Share what you know. Learn what you don't.
> >
> >
>
> --
> Nandakumar
> Systems Analyst
> New York
> (N.Kumar_at_rocketmail.com)
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

I tend to agree with the other poster in terms of normalising...but to answer your other queries:

1. any null columns at the "end" of a row will consume no space. Thus if you have 20 'optional' columns then you won't be penalised in terms of space. This is why it is good to have columns defined from "left to right" so to speak in order of their anticipated frequency, rather than what "looks nice"
2. adding columns can be done with ddl and doesn't not require anything like scanning the table etc...but of course, once you put a value into it then you negate point (a) above. --

   ---

   Connor McDonald "These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 23 1999 - 08:57:24 CDT