Non-relational problem?
Posted by: Richard Nerf
Date: February 08, 2011 09:30PM

I keep encountering a problem in db design that results in awkwardness while inputting new data into a table. A real world example would be trying to graph the daily values of some Mutual Fund accounts, where shares are bought infrequently, but the share value changes daily.

In its simplest form, I there are two tables with columns X,Y, and Z:

Table1
X Y
1 v1
3 v3
7 v7
8 v8

Table2
Z
6
7
9

what I need is a join where the Y value is for a Z lying BETWEEN two successive X values.

Result
Z Y
6 v3
7 v7
9 v8

In the real world, the first table will have ~4000 records and the second ~500,000, so brute force hasn't worked. My "solutions" have been to add an additional column that either points to the "next" record or transcribes its X value, so WHERE only has to reference a single record or two. But this makes it difficult to insert a new record within the sequence in Table 1. Is there a standard way to handle this problem?

Options: ReplyQuote


Subject
Written By
Posted
Non-relational problem?
February 08, 2011 09:30PM
February 09, 2011 01:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.