Re: Navigation question

On Feb 16, 8:08 pm, "dawn" <dawnwolth..._at_gmail.com> wrote:

NB. in what follows, I'm going to deliberately ignore hierarchical queries as the abomination they truly are. "connect by" indeed.

N even more B. SQL is necessarily relevant to the following, but since we've got a query or two under discussion and they're written in SQL, that's where we'll have to stay. Or I could pull a Neo and invent a syntax or two.

>
> I typically conceptualize myself as a user, then sit in between these
> two.

Very good. But applications that directly interact with a user is only one variety of application, although an obviously highly visible one. An approach that "works" when dealing with the comparatively small number of rows a user will directly work with will likely break when presented with a few million rows to chew over.

> Both the user and the disk navigate. Navigation is useful when
> going from one screen to another, one page to another, and maybe even
> one relation to another. I'm still trying to figure that out.
>

User interface navigation - well, maybe. But you *can't* navigate between relations, or even tables (NB taken into account of course).

>
> I understand that you think that.
>

No, I don't *think* that. I *know* that. "Nodes" and their associated trappings were designed out of the relational model, and even SQL managed to stick by that, at least for a while.

> You missed Marshall's first query that corresponds to my first query.
> We each had two queries, which I thought was two round trips for each
> of us.

Hmmm. And you were both wrong for that particular query - one query would have done both, namely

select co.name, ct.emailaddress
from contacts ct, companies co
where co.tin = XXXX
and ct.companyid = co.companyid;

Extend to include other columns and tables as appropriate. One round trip, one result set and no need to hang on to that companyid anywhere.

> I think Marshall is telling me that both of his queries could
> together go to the DBMS at the same time and the DBMS might execute
> them together in some different order.
>

That would be quite an optimisation, and I don't know of a DBMS that does that. References, anyone ?

>
> Conceptually there can be.
>

Maybe in your head. But certainly not in the database. There ain't no nodes to navigate between. Each query stands on its own as a separate question about the database; any "navigation" is an artifact of your program and of your program only. There is no pointer being directed around the database by your queries.

> I do not typically work in SQL DBMS's. What I do is a non-SQL set-
> based query, but where there are no joins at all, but there are
> "links" (navigation) specified so that data can be retrieved from
> "elsewhere."

Indeed. And you are persisting in bashing the square peg into the round hole by using the concepts from one when dealing with the other. There ain't no nodes and there ain't no links.

> This is considered inferior because of the navigation,
> so I'm trying to understand that.

It's inferior because you waste time and energy explaining to your DBMS-of-sorts what these links are, where they go, and then they are hard coded until you specifically go change them. In other words, you waste time and effort concentrating on the "how". In SQL, miserable as it may be in so many ways, most of the time you concentrate on the "what" and leave the DBMS and query optimizer to worry about the "how". And the "how" can change over time without you worrying about it.

[ molto snippage ]

>
> I understand why you think that, but that need not be the case. It is
> very important to separate these two concepts. You navigate the web
> and need not do any iterations when doing so, correct?
>

User interface navigation != database navigation. In an SQL database (that darn NB borne in mind) there is no notion of "here", so if there is no notion of "here", there can't be a notion of "there", and there obviously can't be a notion of how to "get" "there" from "here".

> You can navigate a DBMS (at least the ones I use and I think this is
> also true of SQL) by moving from a foreign key value to other data.

No, not true of SQL (NB...). You don't "move" anywhere.

> If there is no specification for the route (no specification for a
> value to be a foreign key to some other table), then you would not
> have the routes, but if you do have that information, you can find
> your way around the data.

You don't need to state that a column is a foreign key to use it in a join.

> Did any of that make sense?

Sort of. What I could make sense of was wrong though.

> I realize I
> still have more to learn to be permitted to discuss this with people
> who know tons more than I about SQL, but I do have knowledge of models
> other than relational that work very well and I am trying to figure
> out why they are not considered as good from a theory perspective.
>

Because they proceeded from some idea of a best practice based on what was doable at the time, then bolted a theory to it later, not necessarily comfortably. RM started with a theory which proved beyond the implementers and technologies of the day and has been stuck with the compromises they took way back when ever since (IMO).

• Tony