Recursive SQL query?

I'm trying to figure out the best way to build an heirarchial categorystructure kinda like you'd find on yahoo or ebay. Basically I amcreating a number of items and I am going to tie each item via a linktable to another link table that defines the categories and subcategories.

Sample:
categories:
ID = int {autofill/unique/primary key}
Name = char(32)
Desc = char(128)
Property1 = char(64)
Property2 = char(64)

categoryLinks
ID = int {autofill/unique/primary key}
catID = int {required} (pointing to an ID from categories)
parentID = int (pointing to another ID from categories)
Desc = char(128)
Property1 = char(64)
Property2 = char(64)

items
ID = int {autofill/unique/primary key}
Name = char(64) {required}
Desc = char(128)
Property1 = char(64)
Property2 = char(64)

itemLinks
itemID = int (pointing to an ID from items)
categoryLinkID = int (pointing to an ID from categoryLinks)


categoryLinkswill have one entry with parentID = null for the 'top' of anyhierarchical tree. The Desc and other property values may also be null(although I'm going to generally fill in all of them on the top/parententry). I still wrestling with how to deal with identical categoryLinkentries in complex redundant sub-trees but am more interested in anycreative ideas for another delimma.

I would like to be able totreat these kind of like object instances and utilize inheritancewhenever a property of a sub-item is empty (null). i.e. if I had alinking of categories like Computers with links to Desktop and Laptop,and under Desktop had links to Windows, Apple and Linux:

categories:
+----+-----------+
| ID | Desc | Property1 |
+----+-----------+-----------+
| 01 | Computers | technical |
| 02 | Desktop | [NULL] |
| 03 | Laptop | portable |
| 04 | Windows | [NULL] |
| 05 | Apple | [NULL] |
| 06 | Linux | superior! |
+----+-----------+-----------+

categoryLinks:
+----+-------+----------+-----------+
| ID | catID | parentID | Property1 |
+----+-------+----------+-----------+
| 01 | 01 | [NULL] | hightech | (parent - overriding Property1 from categories value)
| 02 | 02 | 01 | [NULL] | (links Desktop under Computer)
| 03 | 03 | 01 | [NULL] | (links Laptop under Computer)
| 04 | 04 | 02 | [NULL] | (links Windows under Desktop)
| 05 | 05 | 02 | [NULL] | (links Apple under Desktop)
| 06 | 06 | 02 | [NULL] | (links Linux under Desktop)
+----+-------+----------+-----------+

let'sjust say I left Property1 blank on the link forWindows and also in it's parent Desktop but had it filled in on theDesktop parent Computers (as shown above). If I wanted to find the Property1 for the sub category but found it blank, I'd like to grab thevalue from the next parent(s) above it that did have a value.

Is there any way to writethat kind of recursive search right in the SQL itself rather than doingtons of lookups in an 'until' type structure in my scripting languageuntil I find a value that's not null?

Ultimately I'd want it tosearch first in the properties for the current item, then the categoryLink then the 'categories' entry for catID from that link, thengo to the parent and continue until either it found a value for agiven property or until parentID was null. I will settle for somethingthat just looks up the categoryLinks tree itself. I know I can do it with aperl or php loop but was just curious if SQL could do anything sofancy. (I'm probably going to be using MySQL but do have access topostgresql also)

The other idea I am toying with is just using afile/directory tree but cringe at the number of file reads I will haveto do when building HTML option lists to get all the descriptions! Imay settle on some combination of the two as this ultimately going tobe for a web tool and I will also be having other associated files witheach category or item. (gif icons, etc.) Either way I'm still goingto have to look backwards to handle the desired inheritance. Any ideasare welcome.

SW

P.S. I'm now playing with another concept of just using keywords for each entry under both categories and items. Basically using a table for the top-level categories, another for individual items and one for keywords. There are then two keyword-link tables, one for category keywords and one for item keywords.
I'm also playing with another set of two tables for generic keyword filters that could be assigned to each category. (a filter name pointing to a particular categoryID and a link table of keywords tied to the filterID that I could then use when creating a search for items related to a desired subset of a given category)

One of the problems I saw with the complex heirarchy is when thestructure gets too deep, it becomes difficult to select a specificitem when you have to keep clicking through category after category toget to the one you want. (just try it on ebay or yahoo! lol)
I added an additional 'type' column on the keywords to categorize howthe keyword relates to the items and even created a special conditionwhere 'type' = 'type' and the keyword= another type value. This then allowsme to include an option add an entire group of keywords of a given'type' with a single link list entry.
This idea will probably give me a lot more flexibility and would avoid problems with redundant sub-trees as well. The user would only have to select a category then a given subset filter to get a manageable list of related items.
But I would still be curious about the recursive search mentioned above.



____________________________________________________________ ________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Re: Recursive SQL query?

Scott Webster Wood wrote:
> let'sjust say I left Property1 blank on the link forWindows and also
> in it's parent Desktop but had it filled in on theDesktop parent
> Computers (as shown above). If I wanted to find the Property1 for
> the sub category but found it blank, I'd like to grab thevalue from
> the next parent(s) above it that did have a value.
>
> Is there any way to writethat kind of recursive search right in the
> SQL itself rather than doingtons of lookups in an 'until' type
> structure in my scripting languageuntil I find a value that's not
> null?

This sounds rather like a poor database design.

Each row of a table should contain one "fact", which can then be merged
via JOIN to incorporate supplementary information pertaining to that
single fact. You shouldn't really need to go recursing through a data
tree to come up with all the parts of a single fact.

For your example, which I've omitted in preference of brevity, I would
suggest adding a property table with two columns: primary key for
property id and property description. Then, instead of storing property
description text separately in your categories and categoryLinks tables,
have a foreign key reference the id column in the property table.

In addition, I'd get rid of your categoryLinks table altogether. In the
categories table, for each row that needs a parent, include a foreign
key that references that same table's primary key. Then, OUTER JOIN the
table to itself later.

If your tree is going to be very deep, keep in mind that some databases
impose (reasonable) limits on the number of JOINs that can be in a
single SQL statement. In the end, you may want to do some processing in
a stored procedure, too, rather than relying on a single SQL query to
provide your entire hierarchy.

Just my $0.02.

Also, since the DBI list isn't really the right place to ask database
design questions, you'll probably want to find a more SQL-centric forum
for future questions. PostgreSQL maintains a pgsql-sql list for
SQL-specific questions, for example.

PostgreSQL would probably be a very good choice for your implementation,
by the way, since it has extensive text searching abilities.

Colin

RE: Recursive SQL query?

The closest thing that comes to this would be Oracle's "Connect By
Prior" syntax. I don't know if other database vendors support this.

-----Original Message-----
From: Colin Wetherbee [mailto:cww@denterprises.org]=20
Sent: Tuesday, April 15, 2008 11:36 AM
To: Scott Webster Wood
Cc: dbi-users@perl.org
Subject: Re: Recursive SQL query?

Scott Webster Wood wrote:
> let'sjust say I left Property1 blank on the link forWindows and also
> in it's parent Desktop but had it filled in on theDesktop parent
> Computers (as shown above). If I wanted to find the Property1 for
> the sub category but found it blank, I'd like to grab thevalue from
> the next parent(s) above it that did have a value.
>=20
> Is there any way to writethat kind of recursive search right in the
> SQL itself rather than doingtons of lookups in an 'until' type
> structure in my scripting languageuntil I find a value that's not
> null?

This sounds rather like a poor database design.

Each row of a table should contain one "fact", which can then be merged=20
via JOIN to incorporate supplementary information pertaining to that=20
single fact. You shouldn't really need to go recursing through a data=20
tree to come up with all the parts of a single fact.

For your example, which I've omitted in preference of brevity, I would=20
suggest adding a property table with two columns: primary key for=20
property id and property description. Then, instead of storing property

description text separately in your categories and categoryLinks tables,

have a foreign key reference the id column in the property table.

In addition, I'd get rid of your categoryLinks table altogether. In the

categories table, for each row that needs a parent, include a foreign=20
key that references that same table's primary key. Then, OUTER JOIN the

table to itself later.

If your tree is going to be very deep, keep in mind that some databases=20
impose (reasonable) limits on the number of JOINs that can be in a=20
single SQL statement. In the end, you may want to do some processing in

a stored procedure, too, rather than relying on a single SQL query to=20
provide your entire hierarchy.

Just my $0.02.

Also, since the DBI list isn't really the right place to ask database=20
design questions, you'll probably want to find a more SQL-centric forum=20
for future questions. PostgreSQL maintains a pgsql-sql list for=20
SQL-specific questions, for example.

PostgreSQL would probably be a very good choice for your implementation,

by the way, since it has extensive text searching abilities.

Colin

Re: Recursive SQL query?

>You shouldn't really need to go recursing through a data
>tree to come up with all the parts of a single fact.

Well that is unless you are wanting to inherit data from linked elements further up a link list without re-posting redundant information.

>Then, instead of storing property
>description text separately in your categories and categoryLinks tables,
>have a foreign key reference the id column in the property table.

>In addition, I'd get rid of your categoryLinks table altogether.

Think like an object oriented programmer for a second. The individual entries in the main Categories table and the individual items tables are 'definitions' of that particular 'object' with some 'default' values filled in. The additional (seemingly redundant) entries on the link table allow the link to 'override' the default value(s) with local, instance specific ones. My goal was as a time/work saving measure (from the administration perspective) to allow 'children' elements to have blank entries where the values would get inherited down from parents.

Besides, my keywords are working nicely, I was just curious about SQL and if there was any conditional recursive looping in the various scripting syntax. (what is the plural of syntax? synti?)

SW




____________________________________________________________ ________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Re: Recursive SQL query?

Scott Webster Wood wrote:
>> You shouldn't really need to go recursing through a data tree to
>> come up with all the parts of a single fact.
>
> Well that is unless you are wanting to inherit data from linked
> elements further up a link list without re-posting redundant
> information.

That's true enough in your situation, but then you trade speed against
data size. I suspect traversing a data hierarchy inside a database,
with SQL alone, won't be too pleasant when it comes to performance.

>> Then, instead of storing property description text separately in
>> your categories and categoryLinks tables, have a foreign key
>> reference the id column in the property table.
>
>> In addition, I'd get rid of your categoryLinks table altogether.
>
> Think like an object oriented programmer for a second. The
> individual entries in the main Categories table and the individual
> items tables are 'definitions' of that particular 'object' with some
> 'default' values filled in. The additional (seemingly redundant)
> entries on the link table allow the link to 'override' the default
> value(s) with local, instance specific ones. My goal was as a
> time/work saving measure (from the administration perspective) to
> allow 'children' elements to have blank entries where the values
> would get inherited down from parents.

That makes sense. I haven't delved into the object-relational
properties of databases, but I wonder if that kind of inheritance is
easier to achieve than it seems?

> Besides, my keywords are working nicely, I was just curious about SQL
> and if there was any conditional recursive looping in the various
> scripting syntax. (what is the plural of syntax? synti?)

If the object-relational thing works out, you may not have to worry
about recursion. I'd suggest investigating that route.

If all else fails and you still want to keep these operations inside the
database, stored procedures are great for that sort of thing. I write
utility and analysis functions in PostgreSQL's PL/Perl all the time, and
I expect it would be much easier to traverse a hierarchy if you were
able to do so in a language like that instead of in SQL.

As for the plural of "syntax", I don't think there is one, but it's
likely possible to pluralize etymologically ancestral versions of it. :)

Colin

Re: Recursive SQL query?

http://www.google.com/search?hl=en&newwindow=1&safe=off&rlz= 1T5GGLL_enUS258US259&q=joe+celko&btnG=Search

On Tue, Apr 15, 2008 at 6:55 PM, Colin Wetherbee wrote:
> Scott Webster Wood wrote:
> >
> > > You shouldn't really need to go recursing through a data tree to
> > > come up with all the parts of a single fact.
> > >
> >
> > Well that is unless you are wanting to inherit data from linked
> > elements further up a link list without re-posting redundant
> > information.
> >
>
> That's true enough in your situation, but then you trade speed against data
> size. I suspect traversing a data hierarchy inside a database, with SQL
> alone, won't be too pleasant when it comes to performance.
>
>
> >
> > > Then, instead of storing property description text separately in
> > > your categories and categoryLinks tables, have a foreign key
> > > reference the id column in the property table.
> > >
> >
> >
> > > In addition, I'd get rid of your categoryLinks table altogether.
> > >
> >
> > Think like an object oriented programmer for a second. The
> > individual entries in the main Categories table and the individual
> > items tables are 'definitions' of that particular 'object' with some
> > 'default' values filled in. The additional (seemingly redundant)
> > entries on the link table allow the link to 'override' the default
> > value(s) with local, instance specific ones. My goal was as a
> > time/work saving measure (from the administration perspective) to
> > allow 'children' elements to have blank entries where the values
> > would get inherited down from parents.
> >
>
> That makes sense. I haven't delved into the object-relational properties of
> databases, but I wonder if that kind of inheritance is easier to achieve
> than it seems?
>
>
> > Besides, my keywords are working nicely, I was just curious about SQL
> > and if there was any conditional recursive looping in the various
> > scripting syntax. (what is the plural of syntax? synti?)
> >
>
> If the object-relational thing works out, you may not have to worry about
> recursion. I'd suggest investigating that route.
>
> If all else fails and you still want to keep these operations inside the
> database, stored procedures are great for that sort of thing. I write
> utility and analysis functions in PostgreSQL's PL/Perl all the time, and I
> expect it would be much easier to traverse a hierarchy if you were able to
> do so in a language like that instead of in SQL.
>
> As for the plural of "syntax", I don't think there is one, but it's likely
> possible to pluralize etymologically ancestral versions of it. :)
>
> Colin
>



--
Matthew O. Persico

Re: Recursive SQL query?

--sm4nu43k4a2Rpi4c
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2008-04-15 18:55:25 -0400, Colin Wetherbee wrote:
> Scott Webster Wood wrote:
> >>You shouldn't really need to go recursing through a data tree to
> >>come up with all the parts of a single fact.
> >Well that is unless you are wanting to inherit data from linked
> >elements further up a link list without re-posting redundant
> >information.
>=20
> That's true enough in your situation, but then you trade speed against da=
ta=20
> size. I suspect traversing a data hierarchy inside a database, with SQL =
alone,=20
> won't be too pleasant when it comes to performance.

Don't forget data consistency.=20

In the OP's design it is clear that NULL means "inherit from parent".
So consider these two cases:

Table categories:
Id Parent-Id Property
1 NULL foo
2 1 NULL

Table categories:
Id Parent-Id Property
1 NULL foo
2 1 foo

In the first case the child (id 2) inherits property foo from the
parent. If you change it in the parent, it will change in the child,
too. In the second case both have the property foo, but they are set on
both explicitely. If you change it in the parent, it will remain the
same in the child.


Now change this to a design with an explicit property table which linked
=66rom all levels:

Table categories:
Id Parent-Id Property
1 NULL prop_1
2 1 prop_1

Table properties:
Id text
prop_1 foo

Now we have no distinction between "inherit from parent" and
"explicitely set to some value which just happens to be the same as that
of the parent".

We can use distinctive entries in the properties table:

Table categories:
Id Parent-Id Property
1 NULL prop_1
2 1 prop_2

Table properties:
Id text
prop_1 foo
prop_2 foo

but that's not quite the same: prop_1 and prop2 can be referenced from
anywhere in the categories tree and the user needs to be able to
distinguish them somehow. So let's drop that for the moment and assume
that properties.text is unique.

Then, when you want to change property on the parent row, you have three
choices:

1) Just change the text in the properties table. This will change it
everywhere in the database, not just in the row and its children.

2) Create a new property entry with the new text, and just change the
foreign key in the parent row to point to this new entry.

3) Create a new property entry with the new text, and recursively change
it in the parent and all descendants.

All of these are different from the orignal design. It's possible that
one of them matches the intentions of OP better than the solution he had
in mind, but you need to be aware of the differences.

There is of course a fourth method:

4) In addition to the foeign key to the properties table, add a flag
which tells whether the value was inherited or set explicitely.=20
On update, recurse as in 3), but change only values with the flag
set.

This restores the semantics of the OP's design.

One other point to consider: In the OP's design, every query for data
may need to go up to the root of the tree to find the value. So that may
be up to depth(tree) additional rows to read *for every query*. In the
cases of 3) and 4) you need to recurse down through the tree for every
update. This may mean up to nodes(tree) additional rows to update for
every update. So you are trading a small improvement in query time
against a potentially huge degradation of update time. So to determine
which is better you need to consider the frequency of queries and
updates, the size and shape of the tree, how many nodes have the
properties explicitely set vs. inherit them, etc.


And finally, this is the perl DBI mailinglist, not an SQL mailinglist.

There is nothing wrong with solving the problem in Perl, you don't have
to do it in SQL ;-).

So, you can for example do individual queries and cache the results (in
the object, if you are creating objects, or in a hash). The upper levels
in the catalog will almost always be in the cache, so you rarely need to
send a query for the parent.

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--sm4nu43k4a2Rpi4c
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIBb1yMdFfQa64PCwRAuSuAKCUvDCq5h+N/na3kZDIOKXpI1KbLACf f0D1
+MeJ2uGm62U6FrmoKSe/9qQ=
=4Ds8
-----END PGP SIGNATURE-----

--sm4nu43k4a2Rpi4c--

Re: Recursive SQL query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I haven't given this too much thought with your situation, but could be something to look into.

Have you looked into using the nested set's model for hierarchies? It allows you to easily pull out
a parent with all it's children in 1 or 2 queries - no need for recursion. It requires more
application code to be able to delete/add/move nodes in the hierarchy but it may server your needs
better if your hierarchy is reasonably deeply nested. You can then use application code to simply
iterate of the retrieved results from parent to child and cache values you may want to use in
children that have NULL values.

Here's some links:
http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html
http://www.openwin.org/mike/books/index.php/trees-and-hierar chies-in-sql

Nath

Scott Webster Wood wrote:
> I'm trying to figure out the best way to build an heirarchial categorystructure kinda like you'd find on yahoo or ebay. Basically I amcreating a number of items and I am going to tie each item via a linktable to another link table that defines the categories and subcategories.
>
> Sample:
> categories:
> ID = int {autofill/unique/primary key}
> Name = char(32)
> Desc = char(128)
> Property1 = char(64)
> Property2 = char(64)
>
> categoryLinks
> ID = int {autofill/unique/primary key}
> catID = int {required} (pointing to an ID from categories)
> parentID = int (pointing to another ID from categories)
> Desc = char(128)
> Property1 = char(64)
> Property2 = char(64)
>
> items
> ID = int {autofill/unique/primary key}
> Name = char(64) {required}
> Desc = char(128)
> Property1 = char(64)
> Property2 = char(64)
>
> itemLinks
> itemID = int (pointing to an ID from items)
> categoryLinkID = int (pointing to an ID from categoryLinks)
>
>
> categoryLinkswill have one entry with parentID = null for the 'top' of anyhierarchical tree. The Desc and other property values may also be null(although I'm going to generally fill in all of them on the top/parententry). I still wrestling with how to deal with identical categoryLinkentries in complex redundant sub-trees but am more interested in anycreative ideas for another delimma.
>
> I would like to be able totreat these kind of like object instances and utilize inheritancewhenever a property of a sub-item is empty (null). i.e. if I had alinking of categories like Computers with links to Desktop and Laptop,and under Desktop had links to Windows, Apple and Linux:
>
> categories:
> +----+-----------+
> | ID | Desc | Property1 |
> +----+-----------+-----------+
> | 01 | Computers | technical |
> | 02 | Desktop | [NULL] |
> | 03 | Laptop | portable |
> | 04 | Windows | [NULL] |
> | 05 | Apple | [NULL] |
> | 06 | Linux | superior! |
> +----+-----------+-----------+
>
> categoryLinks:
> +----+-------+----------+-----------+
> | ID | catID | parentID | Property1 |
> +----+-------+----------+-----------+
> | 01 | 01 | [NULL] | hightech | (parent - overriding Property1 from categories value)
> | 02 | 02 | 01 | [NULL] | (links Desktop under Computer)
> | 03 | 03 | 01 | [NULL] | (links Laptop under Computer)
> | 04 | 04 | 02 | [NULL] | (links Windows under Desktop)
> | 05 | 05 | 02 | [NULL] | (links Apple under Desktop)
> | 06 | 06 | 02 | [NULL] | (links Linux under Desktop)
> +----+-------+----------+-----------+
>
> let'sjust say I left Property1 blank on the link forWindows and also in it's parent Desktop but had it filled in on theDesktop parent Computers (as shown above). If I wanted to find the Property1 for the sub category but found it blank, I'd like to grab thevalue from the next parent(s) above it that did have a value.
>
> Is there any way to writethat kind of recursive search right in the SQL itself rather than doingtons of lookups in an 'until' type structure in my scripting languageuntil I find a value that's not null?
>
> Ultimately I'd want it tosearch first in the properties for the current item, then the categoryLink then the 'categories' entry for catID from that link, thengo to the parent and continue until either it found a value for agiven property or until parentID was null. I will settle for somethingthat just looks up the categoryLinks tree itself. I know I can do it with aperl or php loop but was just curious if SQL could do anything sofancy. (I'm probably going to be using MySQL but do have access topostgresql also)
>
> The other idea I am toying with is just using afile/directory tree but cringe at the number of file reads I will haveto do when building HTML option lists to get all the descriptions! Imay settle on some combination of the two as this ultimately going tobe for a web tool and I will also be having other associated files witheach category or item. (gif icons, etc.) Either way I'm still goingto have to look backwards to handle the desired inheritance. Any ideasare welcome.
>
> SW
>
> P.S. I'm now playing with another concept of just using keywords for each entry under both categories and items. Basically using a table for the top-level categories, another for individual items and one for keywords. There are then two keyword-link tables, one for category keywords and one for item keywords.
> I'm also playing with another set of two tables for generic keyword filters that could be assigned to each category. (a filter name pointing to a particular categoryID and a link table of keywords tied to the filterID that I could then use when creating a search for items related to a desired subset of a given category)
>
> One of the problems I saw with the complex heirarchy is when thestructure gets too deep, it becomes difficult to select a specificitem when you have to keep clicking through category after category toget to the one you want. (just try it on ebay or yahoo! lol)
> I added an additional 'type' column on the keywords to categorize howthe keyword relates to the items and even created a special conditionwhere 'type' = 'type' and the keyword= another type value. This then allowsme to include an option add an entire group of keywords of a given'type' with a single link list entry.
> This idea will probably give me a lot more flexibility and would avoid problems with redundant sub-trees as well. The user would only have to select a category then a given subset filter to get a manageable list of related items.
> But I would still be curious about the recursive search mentioned above.
>
>
>
> ____________________________________________________________ ________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIBb3H9gTv6QYzVL4RAj82AKCjSdX+KHIWePWVgVRYaaC3YUUdJgCg 1LDr
Xsk+bwOSUeo40fdDQUXAg88=
=x58M
-----END PGP SIGNATURE-----

Re: Recursive SQL query?

----- Original Message ----
From: Colin Wetherbee
>> My goal was as a
>> time/work saving measure (from the administration perspective) to
>> allow 'children' elements to have blank entries where the values
>> would get inherited down from parents.

>That makes sense. I haven't delved into the object-relational
>properties of databases, but I wonder if that kind of inheritance is
>easier to achieve than it seems?
>If the object-relational thing works out, you may not have to worry
>about recursion. I'd suggest investigating that route.

>If all else fails and you still want to keep these operations inside the
>database, stored procedures are great for that sort of thing.

Yeah, I actually considered the possibility of creating an accompanying object model in something like perl or the like. perl has a nice capability of 'packing' it's variables, objects are just instances of 'blessed' subroutines and subroutines are stored in hashes. So technically you could pack and store the object instance itself although that would still require a little finess and some recursive lookups at runtime to get the inheritance to work right. But once stored in memory, the parents' info would be availble (but that sorta tosses out the need for a DB other than to initialize the program memory.)

>As for the plural of "syntax", I don't think there is one, but it's
>likely possible to pluralize etymologically ancestral versions of it. :)



Yeah that became quite a joke as I was leaving the office yesterday. I did a quick lookup after writing the email and couldn't find a plural for syntax. I first looked at dictionaries - even foreign ones. I looked through more than a dozen total. I then turned to google in general. Then it hit me that figuring out what the plural of syntax is, is another form of syntax! So I was asking everyone the proper syntax for pluralizing syntax! I received a plethora of responses (there's another interesting one - what's the singular or plethora? plethorum?) from syntax (self-plural), syntaxes, synti ... one person even thought I said 'sin tax' and hid their pack of cigarettes.

SW

SW



____________________________________________________________ ________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Re: Recursive SQL query?

If the singular is
The syntax of the command is....
The command has two syntaxes
The command has two syntacies (like incidies)

On Wed, Apr 16, 2008 at 9:24 AM, Scott Webster Wood wrote:
> ----- Original Message ----
> From: Colin Wetherbee
> >> My goal was as a
> >> time/work saving measure (from the administration perspective) to
> >> allow 'children' elements to have blank entries where the values
> >> would get inherited down from parents.
>
> >That makes sense. I haven't delved into the object-relational
> >properties of databases, but I wonder if that kind of inheritance is
> >easier to achieve than it seems?
>
> >If the object-relational thing works out, you may not have to worry
> >about recursion. I'd suggest investigating that route.
>
> >If all else fails and you still want to keep these operations inside the
> >database, stored procedures are great for that sort of thing.
>
> Yeah, I actually considered the possibility of creating an accompanying object model in something like perl or the like. perl has a nice capability of 'packing' it's variables, objects are just instances of 'blessed' subroutines and subroutines are stored in hashes. So technically you could pack and store the object instance itself although that would still require a little finess and some recursive lookups at runtime to get the inheritance to work right. But once stored in memory, the parents' info would be availble (but that sorta tosses out the need for a DB other than to initialize the program memory.)
>
>
> >As for the plural of "syntax", I don't think there is one, but it's
> >likely possible to pluralize etymologically ancestral versions of it. :)
>
>
>
> Yeah that became quite a joke as I was leaving the office yesterday. I did a quick lookup after writing the email and couldn't find a plural for syntax. I first looked at dictionaries - even foreign ones. I looked through more than a dozen total. I then turned to google in general. Then it hit me that figuring out what the plural of syntax is, is another form of syntax! So I was asking everyone the proper syntax for pluralizing syntax! I received a plethora of responses (there's another interesting one - what's the singular or plethora? plethorum?) from syntax (self-plural), syntaxes, synti ... one person even thought I said 'sin tax' and hid their pack of cigarettes.
>
> SW
>
> SW
>
>
>
>
>
> ____________________________________________________________ ________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
>



--
Matthew O. Persico

Re: Recursive SQL query?

Bloody keyboard....

Anyway,
If the singular is
The syntax of the command is....

Then the plural possibilites are
The command has two syntaxes
The command has two syntacies (like incidies)
The command has two forms of syntax
The command has two syntactic forms
The command has two syntactical forms

I prefer 1 or 5. Either way, just pick one. It's not like this is
French or something where the bloody government has to vote on the
language...

On Wed, Apr 16, 2008 at 1:50 PM, Matthew Persico
wrote:
> If the singular is
> The syntax of the command is....
> The command has two syntaxes
> The command has two syntacies (like incidies)
>
>
>
> On Wed, Apr 16, 2008 at 9:24 AM, Scott Webster Wood wrote:
> > ----- Original Message ----
> > From: Colin Wetherbee
> > >> My goal was as a
> > >> time/work saving measure (from the administration perspective) to
> > >> allow 'children' elements to have blank entries where the values
> > >> would get inherited down from parents.
> >
> > >That makes sense. I haven't delved into the object-relational
> > >properties of databases, but I wonder if that kind of inheritance is
> > >easier to achieve than it seems?
> >
> > >If the object-relational thing works out, you may not have to worry
> > >about recursion. I'd suggest investigating that route.
> >
> > >If all else fails and you still want to keep these operations inside the
> > >database, stored procedures are great for that sort of thing.
> >
> > Yeah, I actually considered the possibility of creating an accompanying object model in something like perl or the like. perl has a nice capability of 'packing' it's variables, objects are just instances of 'blessed' subroutines and subroutines are stored in hashes. So technically you could pack and store the object instance itself although that would still require a little finess and some recursive lookups at runtime to get the inheritance to work right. But once stored in memory, the parents' info would be availble (but that sorta tosses out the need for a DB other than to initialize the program memory.)
> >
> >
> > >As for the plural of "syntax", I don't think there is one, but it's
> > >likely possible to pluralize etymologically ancestral versions of it. :)
> >
> >
> >
> > Yeah that became quite a joke as I was leaving the office yesterday. I did a quick lookup after writing the email and couldn't find a plural for syntax. I first looked at dictionaries - even foreign ones. I looked through more than a dozen total. I then turned to google in general. Then it hit me that figuring out what the plural of syntax is, is another form of syntax! So I was asking everyone the proper syntax for pluralizing syntax! I received a plethora of responses (there's another interesting one - what's the singular or plethora? plethorum?) from syntax (self-plural), syntaxes, synti ... one person even thought I said 'sin tax' and hid their pack of cigarettes.
> >
> > SW
> >
> > SW
> >
> >
> >
> >
> >
> > ____________________________________________________________ ________________________
> > Be a better friend, newshound, and
> > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> >
>
>
>
> --
> Matthew O. Persico
>



--
Matthew O. Persico

Re: Recursive SQL query?

http://encarta.msn.com/encnet/features/dictionary/Dictionary Results.aspx?refid=1861717794

On Wed, Apr 16, 2008 at 1:54 PM, Matthew Persico
wrote:
> Bloody keyboard....
>
> Anyway,
>
> If the singular is
> The syntax of the command is....
>
> Then the plural possibilites are
>
> The command has two syntaxes
> The command has two syntacies (like incidies)
> The command has two forms of syntax
> The command has two syntactic forms
> The command has two syntactical forms
>
> I prefer 1 or 5. Either way, just pick one. It's not like this is
> French or something where the bloody government has to vote on the
> language...
>
>
>
> On Wed, Apr 16, 2008 at 1:50 PM, Matthew Persico
> wrote:
> > If the singular is
> > The syntax of the command is....
> > The command has two syntaxes
> > The command has two syntacies (like incidies)
> >
> >
> >
> > On Wed, Apr 16, 2008 at 9:24 AM, Scott Webster Wood wrote:
> > > ----- Original Message ----
> > > From: Colin Wetherbee
> > > >> My goal was as a
> > > >> time/work saving measure (from the administration perspective) to
> > > >> allow 'children' elements to have blank entries where the values
> > > >> would get inherited down from parents.
> > >
> > > >That makes sense. I haven't delved into the object-relational
> > > >properties of databases, but I wonder if that kind of inheritance is
> > > >easier to achieve than it seems?
> > >
> > > >If the object-relational thing works out, you may not have to worry
> > > >about recursion. I'd suggest investigating that route.
> > >
> > > >If all else fails and you still want to keep these operations inside the
> > > >database, stored procedures are great for that sort of thing.
> > >
> > > Yeah, I actually considered the possibility of creating an accompanying object model in something like perl or the like. perl has a nice capability of 'packing' it's variables, objects are just instances of 'blessed' subroutines and subroutines are stored in hashes. So technically you could pack and store the object instance itself although that would still require a little finess and some recursive lookups at runtime to get the inheritance to work right. But once stored in memory, the parents' info would be availble (but that sorta tosses out the need for a DB other than to initialize the program memory.)
> > >
> > >
> > > >As for the plural of "syntax", I don't think there is one, but it's
> > > >likely possible to pluralize etymologically ancestral versions of it. :)
> > >
> > >
> > >
> > > Yeah that became quite a joke as I was leaving the office yesterday. I did a quick lookup after writing the email and couldn't find a plural for syntax. I first looked at dictionaries - even foreign ones. I looked through more than a dozen total. I then turned to google in general. Then it hit me that figuring out what the plural of syntax is, is another form of syntax! So I was asking everyone the proper syntax for pluralizing syntax! I received a plethora of responses (there's another interesting one - what's the singular or plethora? plethorum?) from syntax (self-plural), syntaxes, synti ... one person even thought I said 'sin tax' and hid their pack of cigarettes.
> > >
> > > SW
> > >
> > > SW
> > >
> > >
> > >
> > >
> > >
> > > ____________________________________________________________ ________________________
> > > Be a better friend, newshound, and
> > > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> > >
> >
> >
> >
> > --
> > Matthew O. Persico
> >
>
>
>
> --
> Matthew O. Persico
>



--
Matthew O. Persico

Re: Recursive SQL query?

http://en.wiktionary.org/wiki/syntax

http://encarta.msn.com/encnet/features/dictionary/Dictionary Results.aspx?refid=1861717794



On Wed, Apr 16, 2008 at 2:03 PM, Matthew Persico
wrote:
> http://encarta.msn.com/encnet/features/dictionary/Dictionary Results.aspx?refid=1861717794
>
> On Wed, Apr 16, 2008 at 1:54 PM, Matthew Persico
>
>
> wrote:
> > Bloody keyboard....
> >
> > Anyway,
> >
> > If the singular is
> > The syntax of the command is....
> >
> > Then the plural possibilites are
> >
> > The command has two syntaxes
> > The command has two syntacies (like incidies)
> > The command has two forms of syntax
> > The command has two syntactic forms
> > The command has two syntactical forms
> >
> > I prefer 1 or 5. Either way, just pick one. It's not like this is
> > French or something where the bloody government has to vote on the
> > language...
> >
> >
> >
> > On Wed, Apr 16, 2008 at 1:50 PM, Matthew Persico
> > wrote:
> > > If the singular is
> > > The syntax of the command is....
> > > The command has two syntaxes
> > > The command has two syntacies (like incidies)
> > >
> > >
> > >
> > > On Wed, Apr 16, 2008 at 9:24 AM, Scott Webster Wood wrote:
> > > > ----- Original Message ----
> > > > From: Colin Wetherbee
> > > > >> My goal was as a
> > > > >> time/work saving measure (from the administration perspective) to
> > > > >> allow 'children' elements to have blank entries where the values
> > > > >> would get inherited down from parents.
> > > >
> > > > >That makes sense. I haven't delved into the object-relational
> > > > >properties of databases, but I wonder if that kind of inheritance is
> > > > >easier to achieve than it seems?
> > > >
> > > > >If the object-relational thing works out, you may not have to worry
> > > > >about recursion. I'd suggest investigating that route.
> > > >
> > > > >If all else fails and you still want to keep these operations inside the
> > > > >database, stored procedures are great for that sort of thing.
> > > >
> > > > Yeah, I actually considered the possibility of creating an accompanying object model in something like perl or the like. perl has a nice capability of 'packing' it's variables, objects are just instances of 'blessed' subroutines and subroutines are stored in hashes. So technically you could pack and store the object instance itself although that would still require a little finess and some recursive lookups at runtime to get the inheritance to work right. But once stored in memory, the parents' info would be availble (but that sorta tosses out the need for a DB other than to initialize the program memory.)
> > > >
> > > >
> > > > >As for the plural of "syntax", I don't think there is one, but it's
> > > > >likely possible to pluralize etymologically ancestral versions of it. :)
> > > >
> > > >
> > > >
> > > > Yeah that became quite a joke as I was leaving the office yesterday. I did a quick lookup after writing the email and couldn't find a plural for syntax. I first looked at dictionaries - even foreign ones. I looked through more than a dozen total. I then turned to google in general. Then it hit me that figuring out what the plural of syntax is, is another form of syntax! So I was asking everyone the proper syntax for pluralizing syntax! I received a plethora of responses (there's another interesting one - what's the singular or plethora? plethorum?) from syntax (self-plural), syntaxes, synti ... one person even thought I said 'sin tax' and hid their pack of cigarettes.
> > > >
> > > > SW
> > > >
> > > > SW
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ____________________________________________________________ ________________________
> > > > Be a better friend, newshound, and
> > > > know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
> > > >
> > >
> > >
> > >
> > > --
> > > Matthew O. Persico
> > >
> >
> >
> >
> > --
> > Matthew O. Persico
> >
>
>
>
> --
> Matthew O. Persico
>



--
Matthew O. Persico