Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Navigation question

Re: Navigation question

From: dawn <>
Date: 17 Feb 2007 20:26:47 -0800
Message-ID: <>

On Feb 17, 7:48 pm, mAsterdam <> wrote:
> dawn wrote:
> > mAsterdam wrote:
> >> dawn wrote:
> >>> I'm still trying to get a straight answer to the question about
> >>> navigation.
> >> Which straight question?
> > For one, from the original post "Do large, production-quality, highly
> > usable and useful, data-based,
> > read-and-write software applications actually exist where there is no
> > code in the software that navigates around the database?"
> I read that one. It did not qualify as a straight question to me.
> Is "take a pointer value, find out what is at the location it is
> pointing to, evaluate that" navigation?

If it does not, then I would like to know what navigation means to others. I "navigate" through a database by moving from foreign key value to a row in a table that it leads to. If navigation means something else, please advise.

> > Some have said "yes" but then removed any OLTP from the system or at
> > the very least (re)define "navigate" so that data from a prior result
> > set may be used to seed another resultset if there is a user event
> > between the two database reads (such as SQL statements).
> Please read the post before you start answering.
> You are repeating stuff I responded to.

In this case I read sequentially and responded as I read. Is that rude? If so, my apologies.

> > For another, "what is wrong with navigation." When I gave an example
> > where there is no user intervention, but multiple SQL statements
> > (which could have been one as Tony pointed out, so I need to revise
> > the example), it is still unclear if there is some reason that
> > navigation of this nature is bad. I'll revise the example here.
> > Let's say that in a single page for the user we need companyid,
> > companyName, emailAddresses [0..m], classifiers [0..m], orderid [0..m]
> > orderPrice [1 for each orderid].
> > I might code this with an alternate toolset with a query that could be
> > mocked up to look more sql-like as
> > select companyid, companyName, emailAddresses, classifiers,
> > Order.orderid, Order.orderPrice from Company where tin='xyz';
> > This would work if the DBMS metadata, data, or some code component has
> > the specification for how to "go from Company to Order. The other two
> > [0..m] attributes have Company as their base "table."
> There will be some navigating going on to find the memory and disk
> locations where the looked for data is stored.

I'm not so interested in physical navigation, but the logical (app code) navigation.

> > Is the following pseudocode that includes navigation a poor way to
> > code this and, if so, why?
> > select companyid c, companyName, emailAddress
> > from Company c, EmailAddr e
> > where c.companyid=e.companyid and tin='xyz';
> > thisCompanyid=companyid;
> > select classifier from CompanyClassifier where
> > companyid=thisCompanyid;
> > select orderid, orderPrice from Orders where companyid=thisCompanyid;
> "poor" could be overly harsh - but, compared to a single query,
> it /does/ introduce some arbitrary ordering, inspired by how the coder
> thinks the data is to be located - which may or may not be the case or
> may change in the future.

What kind of changes would be problematic?

> The single query would have no such dependency.

How would you code the application, perhaps including a total line for the total price after the list of orders presented to the user, if you put this in a single SQL statement? It is certainly possible, but ugh, that cross-product is not pretty.

> >>> Is there something wrong with taking information from one
> >>> query, for example and using it to retrieve data in another query.
> >> If this is the straight question, then the answer is yes.
> >> It depends on the situation how relevant the wrong is.
> > OK, why?
> 1. The 1st query data may be stale at the time of the second query.

Yes, agreed.

> 2. The 2 queries may have optimizer synergy.

So that instead we might have wanted to do what?

> If performance is not an issue,

I definitely care about performance, recognizing tradeoffs.

> 2. won't bite you.
> If actuality isn't an issue, 1. won't bite you.

If 1 might be problematic, then freezing the data first might be required.

> >>> The
> >>> answer to this is "no" for those cases where there is user input
> >>> between the two statements,
> >> How so?
> > If the user lets us know on one screen that they want the company with
> > tin='xyz' whereupon we show them a screen with the above information
> > (tin was not designed for that screen), they make changes, and we read
> > the data (or just the changes) from the screen, are we then permitted
> > to "navigate" by using the companyid we read in to seed SQL statements
> > for updating data, or must we go back to the original data the user
> > gave us? That seems like it would just be a game to do that, but
> > perhaps there is a reason to do so?
> >> The answer to the first query may have changed during user input.
> > If the user has access to change it.
> Or another user has access to change it.

Ah, I understand the point now. How to handle would depend on the specific requirements. In this case, we accessed data using a "secondary key" then used the "primary key" to get the relevant data from another table.

> > We could narrow the question down to situations where that
> > original information remains unchanged,
> > if that helps.
> Helps what? Getting to a situation where it is ok to
> hand-navigate at a logical level without drawbacks?

No, helps in answering the question of whether there are requirements that would make it OK to navigate.

> /Wanting/ navigation without drawbacks, obviously
> as soon as a drawback is mentioned, looking for a rephrase
> that excludes this particular drawback is the way to go.

Yes, because I'm trying to understand if navigating through data is always a bad thing, and, if so, why. So, if you come up with a situation where it is bad, then remove that one. Is there anything left?

> This shows that the up front question wasn't straight but loaded.

Nope. I honestly haven't seen a significant application that did not have data navigation in the application logic. I'll grant that I haven't been heads down in code for weeks on end since 19xx. So, when I was thinking about this, I took a look at some current production code and, yup, there was navigation in it. So, my question was straight forward. The answer was that there are such apps. So, then I'm curious whether navigating through data is always bad and, if so, why.

> >>> but I'm still uncertain if there is
> >>> something wrong with doing this when there is no such event in the
> >>> midst.
> >> What would be the reason for doing this if there is no such
> >> event in the midst?
> > My question is actually what the reason would be for not doing it. I
> > don't advocate that one must code it as above, but my understanding is
> > that any such "navigation" is seen as somehow flawed and I have not
> > yet figured out just what it wrong with it.
> You did not answer my question.
> One answer could be: it is easier to compose the queries that way:

I guess one possible reason is that the designer thinks that way and is unaware of a reason not to navigate in this instance.

> Thinking like I go here get this stuff, then I go there and get that.
> I'd say go ahead on non time-sensitive data on noncritical systems.
> As soon as these conditions change, consider rephrasing
> the query as one.

I would like to pursue this further. I have used what I considered to be oddball star joins in a non-standard product that would somewhat help, but have not used Oracle or standard SQL-DBMS star joins. Is there some type of join that would help us get this data back into a nice resultset? Otherwise if there were 3 e-mail addresses, 4 classifiers, and 10 orders, the resultset I would get back would have 120 rows, right? Then what is the best way to go from there to my single page of data? Thanks. --dawn Received on Sat Feb 17 2007 - 22:26:47 CST

Original text of this message