subtenante wrote:
> On Fri, 15 Jun 2007 00:41:47 -0700, strawberry <zac.carey@gmail.com>
> wrote:
>
>> Perhaps you should take a look at nested sets:
>>
>> http://dev.mysql.com/tech-resources/...ical-data.html
>>
>> There's a good article at sitepoint too
>
> This is a good thing for trees but for linked list, i'm not sure it is
> of great help, it would result in the solution Jon was running away
> from (updating in mean half of the rows for each insert/delete).

But Jon has said:
"100's of nodes. its (SIC) about nesting or if you prefer, tree's."

On Fri, 15 Jun 2007 14:37:40 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>> This is a good thing for trees but for linked list, i'm not sure it is
>> of great help, it would result in the solution Jon was running away
>> from (updating in mean half of the rows for each insert/delete).
>
>But Jon has said:
>"100's of nodes. its (SIC) about nesting or if you prefer, tree's."

True, but if you want to order a table, you have more precisely a
linked list shape (which is a tree, except it has only one branch).
And in that case, using the nested sets pattern makes it quite absurd
: each set contains only one set containing one set containing one
set... therefore the left column lists your elements in one sense, and
the right column lists them backwards. So it provides no advantage :
for each insert/delete, you have to update two values in each row of
half of your columns, instead of updating only one with the initial
shape Jon had in the first place (which is only the equivalent of the
left column in nested sets).

On Fri, 15 Jun 2007 22:47:28 +0800, subtenante
<zzsubtenantezz@gmail.com> wrote:

Sorry got mixed up ! I correct :

So it provides no advantage :
for each insert/delete, you have to update two values in half of your
ROWS (in mean)

"Paul Lautman" <paul.lautman@btinternet.com> wrote in message
news:5dfj0nF34akieU1@mid.individual.net...
> subtenante wrote:
>> On Fri, 15 Jun 2007 00:41:47 -0700, strawberry <zac.carey@gmail.com>
>> wrote:
>>
>>> Perhaps you should take a look at nested sets:
>>>
>>> http://dev.mysql.com/tech-resources/...ical-data.html
>>>
>>> There's a good article at sitepoint too
>>
>> This is a good thing for trees but for linked list, i'm not sure it is
>> of great help, it would result in the solution Jon was running away
>> from (updating in mean half of the rows for each insert/delete).
>
> But Jon has said:
> "100's of nodes. its (SIC) about nesting or if you prefer, tree's."
>

Yes I did.. but nesting can be many things and there are many types of
tree's. subtenante was right that I want to avoid having to update all the
rows for each insert. Since there are no deletes I think a linked list like
method is probably the best but unfortunately I really need a multilinked
list...

Since there can only be one parent and multiple children I think I can use a
variant of the linked list version he suggested. Just keep all the
children's id's in the parent and update only the parent when a new child is
added. Since all the children are specificed in the parent I should easily
be able to traverse through the tree in the manner I wanted.

Here the order by sql won't matter because the order is contained in the
parents field that contains all the children's id's. So this is another
plus.

So far this method seems to be the best and does what I want.

"subtenante" <zzsubtenantezz@gmail.com> wrote in message
news:4oa473h3gmkkmdb43kpg50v4pr0p59k8ek@4ax.com...
> On Thu, 14 Jun 2007 15:35:05 -0500, "Jon Slaughter"
> <Jon_Slaughter@Hotmail.com> wrote:
>
>>Is there such a thing as ordered tables in sql or a simple way to emulate
>>it? i.e., the order in the table matters and one can insert into into the
>>table w.r.t to this order?
>>
>>I suppose I can have a column that sorta simulates the order but the
>>problem
>>is that I don't see how I can simply insert a new row without having to
>>modify all others. If I could simply use the inherent order of the table
>>then it would be much easier but then I think inserting will be very hard.
>>I
>>suppose I could have a unique ID for each one and I think that will work
>>but
>>not completely sure.
>>
>>Not sure if its possible to insert a row inbetween rows? But if I could
>>and
>>I could be sure that SQL would not modify the order then I think I could
>>do
>>it easily... else I end up having to have a field that contains relative
>>information and I'd have to check every single row for it which is not
>>what
>>I'm going to do.
>>
>>Any ideas?
>>
>>Thanks,
>>Jon
>>
>
> I would do something dirty like this :
> table{id, parentid, childid, order, ...}
>
> order is a BIGINT, generated by a procedure. It starts from the row
> with parentid = null, puts order = 1000. (pretending you don't plan to
> have more than the max value of a BIGINT/1000 rows in your table)
> You affect an order of n+1000 for the child of the row that had n as
> order.
>
> When you insert a row, you must update : the child value of its parent
> and parent value of it child, and give the new row an order of
> (parentorder+childorder)/2. That's done with a procedure but it's not
> too heavy and has to modify only two rows.
> If it is not possible to get a proper order (neworder=parentorder OR
> neworder = childorder), call the procedure that reaffects all the
> orders (which will be done only when you have inserted 1000 rows
> between two original rows).

Ok, I think I will use a variant of this. childid will actually be a
contatenation of all the child id's so that I can have multipl children per
parent. I don't think this significantly changes anything you said though.
All I have to do is update the parent by appending the new child id to the
childid's in the parent... I don't even need a procedure.

make sense?

Thanks,
Jon

On 15 jun, 14:21, "Jon Slaughter" <Jon_Slaugh...@Hotmail.com> wrote:
> "subtenante" <zzsubtenant...@gmail.com> wrote in message
>
> news:4oa473h3gmkkmdb43kpg50v4pr0p59k8ek@4ax.com...
>
>
>
> > On Thu, 14 Jun 2007 15:35:05 -0500, "Jon Slaughter"
> > <Jon_Slaugh...@Hotmail.com> wrote:
>
> >>Is there such a thing as ordered tables in sql or a simple way to emulate
> >>it? i.e., the order in the table matters and one can insert into into the
> >>table w.r.t to this order?
>
> >>I suppose I can have a column that sorta simulates the order but the
> >>problem
> >>is that I don't see how I can simply insert a new row without having to
> >>modify all others. If I could simply use the inherent order of the table
> >>then it would be much easier but then I think inserting will be very hard.
> >>I
> >>suppose I could have a unique ID for each one and I think that will work
> >>but
> >>not completely sure.
>
> >>Not sure if its possible to insert a row inbetween rows? But if I could
> >>and
> >>I could be sure that SQL would not modify the order then I think I could
> >>do
> >>it easily... else I end up having to have a field that contains relative
> >>information and I'd have to check every single row for it which is not
> >>what
> >>I'm going to do.
>
> >>Any ideas?
>
> >>Thanks,
> >>Jon
>
> > I would do something dirty like this :
> > table{id, parentid, childid, order, ...}
>
> > order is a BIGINT, generated by a procedure. It starts from the row
> > with parentid = null, puts order = 1000. (pretending you don't plan to
> > have more than the max value of a BIGINT/1000 rows in your table)
> > You affect an order of n+1000 for the child of the row that had n as
> > order.
>
> > When you insert a row, you must update : the child value of its parent
> > and parent value of it child, and give the new row an order of
> > (parentorder+childorder)/2. That's done with a procedure but it's not
> > too heavy and has to modify only two rows.
> > If it is not possible to get a proper order (neworder=parentorder OR
> > neworder = childorder), call the procedure that reaffects all the
> > orders (which will be done only when you have inserted 1000 rows
> > between two original rows).
>
> Ok, I think I will use a variant of this. childid will actually be a
> contatenation of all the child id's so that I can have multipl children per
> parent. I don't think this significantly changes anything you said though.
> All I have to do is update the parent by appending the new child id to the
> childid's in the parent... I don't even need a procedure.
>
> make sense?
>
> Thanks,
> Jon

I've done that sort of ordering. I like tu use mysql to store a kind
of scripting procedures that must be processed in order.
Such as "sum of sales of 2 accounts A and B in account C, delete
Account A, Delete account B.... etc"
As you can see, this are 3 rows in my database.. that a program reads
sequentialy and executes a procedure based on what the record says.
It obvious that if it's not processed in order.. the result is
different or just ends up crashing.
So, I looked back to the old days of BASIC.. yes.. the famous Biginers
All-purpose Symbolic Instruction Code.(not VB)
On that days, programs used to have line numbers... such as
10 Let a="Hello world"
20 Print a
30 goto 20

as you can see, lines where numbered 10 by 10.. so if you needed to
insert lines in the middle, you just add line 15 Let a= a +"!!!!!" to
end up with
10 Let a="Hello world"
15 Let a= a +"!!!!!"
20 Print a
30 goto 20

Well, it was quite easy to end up without space.. so just type
RENUMBER (was it renumber?).. and presto!
10 Let a="Hello world"
20 Let a= a +"!!!!!"
30 Print a
40 goto 30

So, I decided to follow the idea.
All I needed was a renumbering procedure... which I won't type now
because It's 3 in the morning and I'm quite exhausted.. and btw,
you'll get the idea.
what you need is a table with a LINE NUMBER COLUMN.
The increment on the line number, I like it to be a power of 2, as
2,4,8 16,32, 64 and so on has always an integer in it's middle where
to insert. Use a trigger to update that column, not an autoincrement.
Well, let's say that you added stuff to the database, it looks neat..
but wait!.. I need to insert a few row in the middle.. well. depending
on the increment you used, you got space...
But hay.. what if I ran out of space?
Well, renumber the rows.
Renumbering is done with a procedure, that counts the rows, multiplies
it by the increment... (let's say 32) to get the largest line number,
and using a cursor with a select ordered by linenumber DECRementing (<-
VERY IMPORTANT!!!) . I update the last row with that value, subtract
32, fetch, update, subtract, fetch... and so on.
Result, linenumber renumbered and you got your space back.
It's important to do it backwards because if you do it forewards, you
might be stepping over other record's line number and during the
procedure, you end up with 2 records with linenumber, let's say, 64.
So when we get to line 64, what record need's it's line updated?... If
you got the idea, great, if you don't.. trust me.. don't re-invent the
wheel.

Hope it helped you out.
Carlos Troncoso Phillips

On Fri, 15 Jun 2007 18:21:00 GMT, "Jon Slaughter"
<Jon_Slaughter@Hotmail.com> wrote:

>Ok, I think I will use a variant of this. childid will actually be a
>contatenation of all the child id's so that I can have multipl children per
>parent. I don't think this significantly changes anything you said though.
>All I have to do is update the parent by appending the new child id to the
>childid's in the parent... I don't even need a procedure.
>
>make sense?

Ok, sorry i didn't understand you had really a tree structure, I
thought from your first post it was a total order, so Captain was
right in his remark higher.

Anyways i think having a concatenated childid column is a bad id. You
can have all the children with a
SELECT * FROM table WHERE parentid=...
The reason I put it in the first place was only convenience, but now
you have the inconvenient of needing to parse your childid column. And
personnally I would say : don't pretend you wil not delete. Eventually
you will (because of mistakes, or anything). So keep your things
normalized as much as you can : you may have a hard time removing
values inside of concatenated fields (I don't mind doing a dirty thing
from time to time, assuming certain things that are really unlikely to
be overruled, but this is *really* dirty). But i don't know everything
of the case, so if you have really good reasons to do so, do so. But
find out your good reasons before.

On Sat, 16 Jun 2007 07:33:06 -0000, Carlos Troncoso
<schmickcl@gmail.com> wrote:

>The increment on the line number, I like it to be a power of 2, as
>2,4,8 16,32, 64 and so on has always an integer in it's middle where
>to insert.

This power-of-2 thing is very smart !