Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Navigation question

From: mAsterdam <>
Date: Sun, 18 Feb 2007 02:48:12 +0100
Message-ID: <45d7b060$0$332$>

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?

> 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.

> 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.

> 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. The single query would have no such dependency.

>>> 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.
  2. The 2 queries may have optimizer synergy.

If performance is not an issue, 2. won't bite you. If actuality isn't an issue, 1. won't bite you.

>>> 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.

> 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?

/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.

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

>>> 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: 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. Received on Sat Feb 17 2007 - 19:48:12 CST

Original text of this message