Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: retrieve rows in a specified order

Re: retrieve rows in a specified order

From: Dawn M. Wolthuis <dwolt_at_tincat-group.com>
Date: Mon, 22 Dec 2003 22:06:30 -0600
Message-ID: <bs8f0a$eg6$1@news.netins.net>

"Paul" <paul_at_not.a.chance.ie> wrote in message news:MPG.1a5193f1a71043b2989861_at_news1.eircom.net...
>
>
> dwolt_at_tincat-group.com says...
>
> > > OK, so your first point is that adding a field is overly complex in
some
> > > way?
>
>
> > My point is more that the model requires the user (developer) to specify
> > what with other models would be unnecessary components -- both extra
> > attributes and extra relations. This does tend to make for added
complexity
> > in my opinion.
>
>
> And you're suggesting doing it by some sort of linked list or "ordered
> collection"?
>
> I fail utterly to see how maintaining an Order_By_Field in a Relational
> db is any more difficult than rearranging the elements in arrays or
> suchlike.
>

It is not a huge point, but, yes, if the developer simply has to pass along the insert, add to top, add to end, or delete functions and the database maintains the logical order without a developer needing to maintain the list (deleting from the middle requiring logic to zip through the rest of the list and move everything up, for example), there is something to gain from the database handling this feature "itself". Obviously, developers can write the routines to handle all required functionality instead so the resulting system could look the same. So, not a big deal, but a little one none-the-less.

>
> > > Now I'm confused.
> > > First you say that adding an Order_By_Field field to a db is too
> > > complex, then you go on to propose what, to me, looks like a very
> > > complex solution for what is a relatively simple task.
>

Use of just about any database other than an RDBMS is typically simpler from the standpoint of both the initial developers and those maintaining an application, don't you think? (I'm not questioning whether you think, but whether you agree ;-)

>
> > My point is more that if you were to add data to an ordered list, the
> > database would take care of the ordering field rather than a developer
> > having to maintain it.
>
>
> This is true enough, though in much the same way as adding an ordering
> field and never changing it would not require a developer to maintain
> it.
>
>
> The man said in his posts, that this order has got to be able to be
> changed in different shops and/or at different times - this to my tiny
> brain implies somebody (shop manager usually) modifying some part of the
> data so that it appears on his web pages/app in the order that he wants
> - No?
>

Yes, the end-user inserts, adds, deleted, changes the list to their heart's content, and the application passes the appropriate commands to the database while the database handles the logical ordering rather than the application developer doing such. While I have done work with relational databases of late, it has been a while since I did extensive work with them, but from what I recall, all RDBMS's I have used required that any such "sort order" types of fields be maintained as any other number would be -- by way of the application rather than the database "understanding" ordinals for lists.

>
> > If one inserts into an array, the array handles the
> > indexing (ordering) of the field behind the scenes. That seems natural
and
> > reasonable to me.
>
>
> If one simply *_adds_* to an array, then yes. Howver, if one wishes to
> move element 17 to 10, 10 to 5, 5 to 11 and then 11 to 17, that will
> require some sort of programming or application which does this for the
> end user - No?
>

An application -- yes, but the application need not touch or care about whatever was in slot 29 or anywhere else if sending an insert and delete to the database that understands ordinals (no matter how the database implements that feature).
>
> > I'm proposing using a conceptually simpler model than the
> > relational model -- one that thinks more like a person thinks and
speaks,
> > including such constructs as functions that map to ordered lists within
the
> > model itself.
>
>
> But when these orders are changed (by the manager) *_something_*
> somewhere relating to the data held about his products will have to
> change - No? And messing about with array elements is, in my experience,
> trickier than using SQL.
>

Not in my experience. If one works with the U2 databases from IBM, for example, a field (column) can be designated as "multivalued" and then the developer simply adds, removes, etc from the list quite handily, as if working with a Java map (which is, mathematically, a function and therefore a relation), for example.

>
> > > How is *_ANY_* system supposed to know how to order the products
unless
> > > you tell the system? AIUI, you are saying to do by "ordered
> > > collections of foreign keys to the product database", whatever that
> > > means!
>
>
> > Sure, a user needs to insert before, after, between, or whatever, but no
> > developer needs to tell the database how to handle that if the model
> > implemented by the database understands ordered lists. Almost every
> > database other than relational databases implements such a model.
>
>
> But given any product that claims to be an RDBMS (Oracle, Sybase, <long
> list...>), there would be no need for the developer to tell the db how
> to handle anything - the results would arrive sorted by the manager
> decided order - the developer would just write an "ORDER BY" clause in
> his SQL - he wouldn't have to worry about the implementation of the db.
>

How would the order of the items on the page be reflected in the database? If the end-user removes item 17, doesn't the developer have to include the logic to bump 18 up to 17 and 19 to 18, etc? Or is there a function that I'm missing in an RDBMS that handles attributes of a type like "ordinal" and automatically handles the ordering for the application developer? In the non-RDBMS I'm referring to (even those that insist on calling themselves RDBMS's) the developer isn't telling the database how to store the data -- it is just that lists are types of attributes that are dealt with handily by the database and then, yes, the end-user can issue a query (SQL or preferably a list-savvy query language) to retrieve the data without any developer intervention. [a poorly written statement, but I hope you catch the meaning anyway]

>
> > By an ordered collection of foreign keys, this could be, for example, an
> > array of product ID's. As an array, the product ids would be ordered.
So,
> > if you asked the database what was stored in the ProductsForWebCatalog
field
> > in the database, it might tell you
>
>
> Indeed it would - however, how are you going to determine in advance the
> number of elements in your array? With SQL this is a non-issue.
>

Why would I need to determine in advance the number of elements. Again in models that are implemented by the likes of IBM's U2 databases, the size of elements, size of records, size of files, size of arrays, etc are all variable -- developers and DBAs (if such were necessary in such an environment) need not know any of this nor specify it in advance.

> Furthermore, if you want to change the ordering (which was, after all,
> the fundamental question being asked by the original poster), the amount
> of background knowledge required by any programmer is IMHO, at least as
> much for your system as for an RDBMS.
>

I rather doubt that, however I do not doubt that students come out of colleges now knowing the RDBMS model and pretty much nothing about others that are currently in production except to hear that they are either "outdated" or "too new". I hope to help change that so that students do have an understanding of more of the available options and the pros and cons of various choices of databases they have.

>
> > CS394A
> > NH283
> > ANOTHERPRODUCTIDHERE
> > If you asked for the ProductsAndPages, you could get
> > CS384A 1
> > NG283 1
> > ANOTHER PRODUCTIDHERE 2
>
> > to show that the first two products were being place, in that order on
the
> > first page of the web catalog and the last was on the second page.
>
>
> This strikes me as being very dangerous in that the application
> developer has to know the browser settings of the user and/or what a
> HTML page is with respect to the data it is querying - this makes life
> very difficult IMHO.
>

That is what the question was -- my preference would be to order the elements and have the GUI determine when to paginate. So, I'll agree with you there.
>
> > The
> > end-user can use a GUI to click and drag products into whatever order
they
> > want
>
>
> There is no reason why any half-competent programmer who uses SQL
> couldn't allow his end-users to do the same.
>

Absolutely! The difference is in the time it takes to develop such a system and the time it takes to maintain it. This is an issue of software development productivity that prompts me to provide an alternative to an RDBMS for many/most/almost all applications that now use RDBMS's.

>
> > (this doesn't change the fact that the PRODUCTS file is not ordered)
> > and can assign whatever page number they want.
>
>
> We have already determined that in either system, the order in which the
> products come out in a select can be determined by the shop manager. You
> seem to be making dangerous assumptions about web page numbers which
> IMHO, the application developer shouldn't be concerned about - No?
>

I agree -- I thought I read that in the question -- that the end-user wanted to decide on which page s/he wanted particular products to show. If I misread the question, then strike that and simply keep the ordering issue.

>
> > All of this information
> > could be stored in the WEBPAGES file.
>
>
> What is this WEBPAGES file?
>
>
> > Again, this is not a relational
> > database design unless using relation-valued attributes somehow, but it
is
> > possible in almost all non-RDBMS databases being used in production by
> > companies today.
>
>
> To me (in my naivity perhaps) it is very simple.
>
> The database provides the data - it is up to the web server to format it
> - i.e. a section per product, - obviously you don't want to be sending
> hundreds of K over the internet - so you allow them to select all cars
> that are yellow and have a cc over 1.4l or whatever - the formatting of
> web pages is a different issue.
>
>
> > > Somewhere in the system, an ordering is going to have to be stored.
>
> > Yes, but the database can take care of that -- the developer need not
have
> > to.
>
>
> Jesus! The developer is going to have to allow the user to change this
> ordering criterion - he is going to have to provide some sort of
> programmatical interface to the system to allow the manager to do this.
>

Now, don't go getting me confused with Jesus. Yes, but hopefully I have addressed this already by indicating that most non-RDBMS's work handily with ordinals and lists, while (I think) developers using RDBMS's need to code such logic for every ordinal column they use. I am unaware of any relational operators that handle such lists nicely, but please correct me if I am missing something.

> I am arguing that what you are saying about "ordered collections" and
> rearranging them would require at least as much programming as an RDBMS.
>
>
> > > Can you expand on what you mean here exactly? What is normalising on
the
> > > fly?
>
>
> > For data that is stored in something other than what was traditionally
known
> > as first normal form (that is, for data stored in systems that permit
> > lists),
>
>
> i.e. arrays?
>
>
> Listen, I'm tired. I'll try and pursue your other points tomorrow, but I
> really think that you are very unclear in your thinking.
>
>

I'm hoping that I'm just unclear in my writing and that my thinking is dead-on, so thanks for your patience trying to understand and/or right me in this matter. I guess my question is: Do developers who write applications using RDBMS's typically (or even ever) have the ability to let the database keep track of what the 7th item is in a list, or do they have to write the logic to put a 7 in the data somewhere and also write logic that removes a value in the "array" which results in moving up the later values? Now I'm tired, so I hope this helps. Thanks, Paul. --dawn

>
> Paul...
>
>
>
> --
> plinehan y_a_h_o_o and d_o_t com
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
> Please do not top-post.
>
> "XML avoids the fundamental question of what we should do,
> by focusing entirely on how we should do it."
>
> quote from http://www.metatorial.com
Received on Mon Dec 22 2003 - 22:06:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US