Re: data modeling conundrum

In article <8v1mvt$5o7$1_at_nnrp1.deja.com>,   bdeneaud_at_my-deja.com wrote:
> I need to store large sets of number pairs like 1.01, 0.01; 1.02,
 0.02;
> etc... in my database. I need to store about 400 of these pairs for
> each parent record. (parent records growing at 100,000 rows a year)
> How can I store these efficiently and what is the best datatype to
 use.
>

I see 2 scenarios:
1) Your pairs represent 400 different attributes of a parent (ie. headquarter location, sales office location, main plant location, etc.)

2) Your pairs represent 400 different measurements of the same attribute (min and max daily parent temperatures for a year).

In case #1, you should really put the attribute pairs in the parent record. In oracle7, you were limited to 256 columns per record (8 has 1000, I think), so you might have to "link" together several records.

Putting each attribute in it's own column allows you to ask things like "what is the average value of attribute #1 across all parents?". The processing overhead of parsing some sort of agglomerate structure will quickly outweigh any space savings.

In case #2, I would create a child table (parent_key, val_1, val_2). Yes, this will take up space - a lot based on your growth figures. However, you're putting this data in so you can eventually get it *out*. A child table allows you to ask "what is the average val_1 for parent x". While you certainly could concatenate the pairs into, say, a varchar2:
a) you'd have to separate them in pl/sql in order to perform calculations on them.
b)you could never, ever use an index to say "give me all val_1's greater than Z".

Hope this helps
b) any qu

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 16 2000 - 17:16:34 CST