Re: retrieve rows in a specified order

From: Dawn M. Wolthuis <dwolt_at_tincat-group.com>
Date: Sun, 21 Dec 2003 21:38:42 -0600
Message-ID: <bs5p09$8jr$1_at_news.netins.net>


"Paul" <paul_at_not.a.chance.ie> wrote in message news:MPG.1a4fe4a636ea309c989853_at_news1.eircom.net...
>
> dwolt_at_tincat-group.com says...
>
>
>
> > > Imagine that each row represents one product in a shop. Now I need to
> > > let the shop owner decide in which order the products appear on a web
> > > page. He needs to be able to freely change this order at any time and
> > > move any product to any position he wants.
>
>
> > If you are compelled to use a relational database, then you would need
to
> > add an attribute that is used for ordering. Relational databases tend
to
> > have lots of attributes and tons of tables not necessary in other
flavors of
> > data storage systems.
>
>
> 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.

>
> > So, if you may select any database, then you might want to select one
that
> > can store ordered collections. Your product table would then not be
> > ordered, but a table that stores your web pages could include ordered
> > collections of foreign keys to the product database. Databases that
would
> > permit this are just about all except relational: XML databases
(Berkeley
> > DB-XML or Xindice, for example), object databases, PICK (such as IBM U2)
> > databases and others (adabas, progress?).
>
>
> 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.
>
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. 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. 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.

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

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

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. The end-user can use a GUI to click and drag products into whatever order they want (this doesn't change the fact that the PRODUCTS file is not ordered) and can assign whatever page number they want. All of this information could be stored in the 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.

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

>
> > Even relational databases now permit arrays or other types of
collections in
> > attributes. However, SQL-99 is not implemented far and wide (in
particular,
> > it is not used for ODBC). The non-relational databases typically
> > normalize-on-the-fly for SQL or ODBC so that the nested data can be seen
> > either as stored or in 1NF.
>
>
> 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), SQL-92 (and therefore ODBC) is only possible (other than providing extensions as was done with SQL-99) by permitting the SQL statements to perceive the data as if it were in 1NF, even if the data model in use is not of the RDBMS ilk. So, unlike RDBMS's that are extended to included nested data of one type or another, which then extend SQL in some proprietary way or with SQL-99 in order to handle this (e.g PostgreSQL), these non-1NF databases typically provide a logical view of their structures which fits with the relational model enough to permit SQL to see the data as if it were in 1NF. Did that clarify? IBM's Universe database, for example, provides ODBC capability that executes SQL against non-1NF stored data, but the SQL statements execute against logical structures that are normalized, making the database appear to have a zillion rows when it might have only a 100,000 records (since the records include nested data). Let me know if this was not stated well-enough -- I'm typing fast and not proofing (sorry). Cheers! --dawn

>
>
>
> Paul...
>
>
> > --dawn
>
>
> --
>
> plinehan x__AT__x yahoo x__DOT__x com
>
> C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro
>
> Please do not top-post.
Received on Mon Dec 22 2003 - 04:38:42 CET

Original text of this message