Re: retrieve rows in a specified order

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.

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

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?

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

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

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

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

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.  

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

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

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

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

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.

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