Re: Design Question - variable number of elements per table

Alfie wrote:

> Hi,
>
> I wondered if there was some sort of structure which enabled a variable
> number of elements per table - I'm pretty sure there isn't - but maybe
> there's a way.
>
> Anyway, say I have a stock which is traded. I have any number of deals
> struck at various prices over time. So I have a "Trade" entity thus
>
> Stock Id
> Time
> Amount
> Price
>
> I will take various snapshots of the trading history to work with.
>
> Each snapshot can have a variable number of prices at which trades were
> struck with associated volume (sum of trades).
>
> Can anyone think of a way I can have a meaningful 'Snapshot' Table such as
>
> Stock Id
> Snapshot time
> +a variable number of Amount/Price pairs attached.
>
> I'm implementing the DB on a standalone PC and I'm worried that as I will
> end up having hundreds of thousands - probably millions of Amount/price
> pairs, performance will suffer.
>
> I don't mind the restriction that I can only access via the Stock
> Id/Snapshot time index - perhaps I can store the rest of the data as some
> variable length text field? Perhaps I can then parse this and extract the
> pairs as and when needed.
>
> Any ideas/opinions appreciated.
>
> I'm working with 10g.

Look at the object features in Oracle such as nested tables and varrays. I believe they can easily model what you describe.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)