Re: Navigation question

On Feb 16, 5:04 pm, "Tony D" <tonyisyour..._at_netscape.net> wrote:
> 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.

If you insist. My original question was not about SQL and SQL is not the language I choose when working with a DBMS these days. I am not an SQL expert (obviously).

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

Yes, but it is the one I am thinking of when asking my question of why navigation is seen to be so wrong by so many.

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

Agreed. Might it also be the case that the approach used for massive amounts of data might not be the best when working with OLTP software.

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

Well, brother, that is precisely what I do (at the logical, including metadata, level). I just don't use a native SQL-DBMS to do it.

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

I am quite sure I did not say that we needed to use the relational model in my question. I don't understand what is wrong with navigation, although I do understand places where it is not a good approach.

> and even SQL

Again, not my usual playground, so I'm willing to attempt to use it for illustration.

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

Ah, very good. I agree that would have worked, but the approach does not generalize well for the original example I gave where adding the orders into that mix would then put a cross-product in the result set. That is likely why Marshall split each piece out. I then took the first two of the SQL statements and rewrote them as two other statements. If we were getting the e-mail addresses and company classifications (with multiple values possible as classifiers) then we would need to split out to another statement to avoid the crossproduct.   So, these two statements are not as good as one for this case, but the approach generalizes.

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

Agreed in this example.

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

I just read up on batches. It sounds like you get a single execution plan for a batch, so perhaps that is what he is talking about? I have not been able to find java code showing use of a batch for retrieval, only for update (and so far the examples have had iteration, oddly enough). I was trying to figure out how the assignment of the resultset works when executing multiple select queries in a single batch. I'm not catching yet.

> > Conceptually there can be.
>
> Maybe in your head. But certainly not in the database.

Since when does a database conceptualize?

> There ain't no
> nodes to navigate between.

Conceptually there are. (btw, I just said that with a wink, some folks here never take to my humor, so I thought I'd point that out)

> Each query stands on its own as a separate
> question about the database;

That is what I thought. I'm trying to figure out what is possible with SQL batches, but still do not see how multiple result sets would be assigned properly if executed in a batch. I only used jdbc 1.0 and it might be that batch came in 2.0. The java examples I have see so far are for updates.

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

Not in SQL, agreed. Again, I'm trying to bridge the gap between worlds here and my question of navigation is not really "why is navigation considered bad when used with SQL" but "why is navigation considered a bad thing to the extent that many relational proponents pooh-pooh navigation in a database altogether?"

> > 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 are definitely some shared concepts. I'm not actually trying to use navigation with SQL, as I am not currently using SQL, but accepting that is where there is a common language for examples.

> There ain't no nodes and there ain't no links.

Conceptually there are nodes and logically in my toolsets there are 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,

much like specifying joins, so I'm not gonna accept that point

> where they go, and then they are
> hard coded until you specifically go change them.

You mean like those hard-coded joins in select statements, the ones you specify over and over again because the join information is not specified as descriptive information in the metadata?

> In other words, you
> waste time and effort concentrating on the "how".

Not any more than when you specify a join, except that conceptually I do think of it as "how." (And I don't want to get into the "what, not how" debate right now)

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

I understand that is the philosophy and I can appreciate it. But imagine if you could write something like

select customerid, emailAddresses, Order.orderid from Customer;

And imagine the resultset as a JavaScript associative array or a dynamic array so that you do not get a cross-rpdocut of emailAddresses and orderid. In the above example, the first two have Customer as their base table, while the orderid is in the Order table.

This is, effectively, what can be done with some DBMS's. But you can see that there is "navigation" which is specified once to indicate what would otherwise be the join information, instead of with each query.

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

This defnitely must be the understanding of others too, because it seems obvious that there is any user event or even any event that could occur after you retrieve a result set, you get to navigate, even if using data that was retrieved in the last resultset, without it being considered navigation. So, when theorists suggest they are opposed to navigation, they are not opposed to navigating a database as long as user events separate the read of some information from its use in navigating. So that is one of the things we can use to finish the sentence "we aren't really navigating through the database because..." .

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

Again, SQL databases are not the reason for my question.

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

I guess because you cannot specify your joins to the DBMS, the DBMS has no knowledge of the linkages, but once you give it join information... OK, I agree that from your perspective, there is nothing in the logical/code that indicates movement.

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

I know.

> > Did any of that make sense?
>
> Sort of. What I could make sense of was wrong though.

What about this time -- any more sense, anything more accurate in this round?

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

Yes, we could discuss the benefits of starting with practice and backing into theory compared to starting with theory and applying it to practice. I'd like to see the two align better. But, given a choice, I would rather do theory with the approach that started with theory and rather do practice with the approach that started as a practice (so far, but I'm not married to that and would like to see beautiful theory with excellent practice align). cheers! --dawn Received on Sat Feb 17 2007 - 16:30:59 CET