Recursive join - blind alley?

[Mike MacSween]

Regular viewers may want to turn off now.

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join. I've done a fair
bit of research. Which frequently brings up the words 'Joe Celko' and 'BOM'.
I'm not sure that the BOM is exactly what I need, but it's close. I actually
think an adjacency list is a better solution in this case than nested sets,
though probably implemented with (at least) 2 tables. This area may or may
not be the problem. As a matter of fact the same problems would arise, I
think, with a static structure, e.g:
Production-<Events

In the 'real world', the person I'm doing this for, a musician may be booked
for a show, will need to be booked for some/all of the events in that show,
but may be paid at the show level. i.e. they are booked for 7 shows, but are
paid a total of $548.34 for all shows together. Involved at a 'child' level
but paid at a 'parent' level. Here there seems to be duplication, we KNOW
they are involved with the parent if they are involved with any of the
children, and so on all the way up the hierachy.

Some people may be involved with a parent but NOT it's children. An arranger
writes the orchestrations for a week long show, but doesn't turn up on the
shows. So there is no certainty that people can only be involved at the
lowest child level.

But if on some occasions people are involved at a child level and no parent
level (paid by the individual show) whereas on others they are involved with
both (booked for the gigs, paid by the week) there's a difference, sometimes
there are duplicate data, sometimes there aren't.

OK, if it's just payment that's the problem, spin if off into 2 junction
tables:

Event -<Fee>-Musician

Event-<Booked>-Musician

That separates the two things that are getting muddled. But this now looks
strange. People are now getting paid for jobs they might not be involved in.
How so? It would actually accomodate copyright payments and suchlike (which
aren't part of the requirements) but it still looks strange. And I suspect
complex to implement.

I realise this isn't the first time I've asked for help on this, but I have
done a huge amount of pondering/studying and am asking for help because I
think I may have thought myself into a corner. Another perspective would be
valued.

TIA, Mike MacSween

[Fletcher Arnold]

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

Regular viewers may want to turn off now.

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join. I've done a fair bit of research. Which frequently brings up the words 'Joe Celko' and 'BOM'. I'm not sure that the BOM is exactly what I need, but it's close. I actually think an adjacency list is a better solution in this case than nested sets, though probably implemented with (at least) 2 tables. This area may or may
not be the problem. As a matter of fact the same problems would arise, I
think, with a static structure, e.g:
Production-<Events

In the 'real world', the person I'm doing this for, a musician may be booked for a show, will need to be booked for some/all of the events in that show, but may be paid at the show level. i.e. they are booked for 7 shows, but are paid a total of $548.34 for all shows together. Involved at a 'child' level but paid at a 'parent' level. Here there seems to be duplication, we KNOW
they are involved with the parent if they are involved with any of the
children, and so on all the way up the hierachy.

Some people may be involved with a parent but NOT it's children. An arranger writes the orchestrations for a week long show, but doesn't turn up on the
shows. So there is no certainty that people can only be involved at the
lowest child level.

But if on some occasions people are involved at a child level and no parent level (paid by the individual show) whereas on others they are involved with both (booked for the gigs, paid by the week) there's a difference, sometimes there are duplicate data, sometimes there aren't.

OK, if it's just payment that's the problem, spin if off into 2 junction
tables:

Event -<Fee>-Musician

Event-<Booked>-Musician

That separates the two things that are getting muddled. But this now looks
strange. People are now getting paid for jobs they might not be involved in. How so? It would actually accomodate copyright payments and suchlike (which aren't part of the requirements) but it still looks strange. And I suspect
complex to implement.

I realise this isn't the first time I've asked for help on this, but I have done a huge amount of pondering/studying and am asking for help because I
think I may have thought myself into a corner. Another perspective would be valued.

TIA, Mike MacSween

If you're looking for another perspective - here's a different direction to
consider:

Do not rely on the table structure to provide the information you require -
use a system of product codes and masking instead.

Here are some product codes you might use:

WT2004-XX-XX-XX-000 World Tour 2004
WT2004-US-XX-XX-000 US leg of 2004 World Tour
WT2004-US-NJ-HB-000 Hoboken Concert
WT2004-US-NJ-HB-001 Hoboken Concert - Dress rehearsal
WT2004-US-NJ-HB-002 Hoboken Concert - Show 1
WT2004-US-NJ-HB-003 Hoboken Concert - Show 2
WT2004-US-NJ-HB-101 Hoboken Concert - All shows incl. rehearsal

WT2004-EU-GE-XX-000 Germany
WT2004-EU-GE-HD-000 Heidelberg Concert
WT2004-EU-GE-HD-003 Heidelberg Concert - Show 2

This system allows you to code the products so you can retrieve the detail
you need. For example, find all the payments for the 2004 world tour - you
just need WHERE ProductCode LIKE "WT2004*" or perhaps you need all years,
all concerts in Germany - WHERE ProductCode LIKE "??????-EU-GE-??-???"

I think you just need to accept there some things are too complicated to be
handled by table structure alone and some form of meaningful analysis codes
are needed. If you consider how a large supermarket might handle, say, a
specially priced multi-pack of fruit juice. For pricing reasons, they need
a unique product code with a price, but for other reasons they may need to
know that they sold a pack with a total volume of 4 litres. The coding of
the product will let them analyse how much apple juice, orange juice, all
juices, how many multi-packs etc have been sold but no use will be made of
relational structures (eg you would not need tblMultiPack and tblItem).

I hope you are able to take this idea and apply it to your database - it
should make certain aspects a million times easier, more flexible and more
in line with how products are traditionally categorized.

</MyTuppenceWorth>

Fletcher

[Mikito Harakiri]

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bt*********@hercules.btinternet.com...

WT2004-XX-XX-XX-000 World Tour 2004
WT2004-US-XX-XX-000 US leg of 2004 World Tour
WT2004-US-NJ-HB-000 Hoboken Concert
WT2004-US-NJ-HB-001 Hoboken Concert - Dress rehearsal
WT2004-US-NJ-HB-002 Hoboken Concert - Show 1
WT2004-US-NJ-HB-003 Hoboken Concert - Show 2
WT2004-US-NJ-HB-101 Hoboken Concert - All shows incl. rehearsal

WT2004-EU-GE-XX-000 Germany
WT2004-EU-GE-HD-000 Heidelberg Concert
WT2004-EU-GE-HD-003 Heidelberg Concert - Show 2
Wouldn't
WT2004 World Tour 2004
WT2004-US US leg of 2004 World Tour
WT2004-US-NJ-HB Hoboken Concert
WT2004-US-NJ-HB-001 Hoboken Concert - Dress rehearsal

be more natural representation?

[Mike MacSween]

Fletcher and Mikito

Thanks. Good ideas, but I'm pushing this forward. I know what you mean about
table structure not always doing the whole job. But sometimes thinking aloud
like this gets me forward.

I think the recursive join/BOM may be the way. Forget payments. If a
musician is 'booked' for an 'element' (at last, I've found the right word)
of a production, then they are ipso facto 'booked' for that elements parent,
grandparent etc. So the idea that a musician can't be booked for Tuesday's
show (child element) and for the week's run of the show (parent element)
makes sense. We know they're booked for part/all of the week's run if
they're booked for the Tuesday (+Wednesday etc.). As a matter of fact Joe
Celko's nested sets might very well be a better solution here, as it looks
easier to drill up/down the structure using standard SQL. There might be
some quite complex triggers to write to enforce this though. Time will tell,
but it's a worry at the back of my mind. A common 'use case' is the client
books musicians for, let's say, a weeks run of a show before she has the
details. e.g. - 'Mike, can I book you for Annie 15-22 of November, it'll be
about £400 for the week but I haven't got the details yet'. In my scenario
when the dates are known she (or the system) will have to unbook me for the
week's run (which is now a parent element) and book me for each date. It
might be easy or not.

But the payments is easier (at least at an ER model) that I thought. I
simply group a set of 'musician booked for element' together into a payment
group. So people may get paid at the 'parent' level, but only
coincidentally. Other times the payment group may be completely unrelated to
the element/sub-element structure. For instance a month long show,
consisting of 4 single week runs, each consisting of 7 shows. That's the BOM
type structure. But the trumpet player can't make any of the Tuesday shows.
So she puts a 'dep' in. The dep's 4 tuesdays fees are 'grouped' into one
payment. My mistake was that I was equating payments with the organisational
structure of the thing.

I need to investigate this a lot further of course. My experience of finding
an 'ideal' schema is that the implementation becomes a nightmare,
un-updateable queries and so on. Or difficult deletions.

Yours comments, as always, most welcome.

Yours, Mike MacSween

"Fletcher Arnold" <fl****@home.com> wrote in message
news:bt*********@hercules.btinternet.com...

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

Regular viewers may want to turn off now.

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join. I've done a fair

bit of research. Which frequently brings up the words 'Joe Celko' and

'BOM'.

I'm not sure that the BOM is exactly what I need, but it's close. I

actually

think an adjacency list is a better solution in this case than nested

sets,

though probably implemented with (at least) 2 tables. This area may or may not be the problem. As a matter of fact the same problems would arise, I
think, with a static structure, e.g:
Production-<Events

In the 'real world', the person I'm doing this for, a musician may be

booked

for a show, will need to be booked for some/all of the events in that

show,

but may be paid at the show level. i.e. they are booked for 7 shows, but

are

paid a total of $548.34 for all shows together. Involved at a 'child'

level

but paid at a 'parent' level. Here there seems to be duplication, we KNOW they are involved with the parent if they are involved with any of the
children, and so on all the way up the hierachy.

Some people may be involved with a parent but NOT it's children. An

arranger

writes the orchestrations for a week long show, but doesn't turn up on the shows. So there is no certainty that people can only be involved at the
lowest child level.

But if on some occasions people are involved at a child level and no

parent

level (paid by the individual show) whereas on others they are involved

with

both (booked for the gigs, paid by the week) there's a difference,

sometimes

there are duplicate data, sometimes there aren't.

OK, if it's just payment that's the problem, spin if off into 2 junction
tables:

Event -<Fee>-Musician

Event-<Booked>-Musician

That separates the two things that are getting muddled. But this now looks strange. People are now getting paid for jobs they might not be involved

in.

How so? It would actually accomodate copyright payments and suchlike

(which

aren't part of the requirements) but it still looks strange. And I suspect complex to implement.

I realise this isn't the first time I've asked for help on this, but I

have

done a huge amount of pondering/studying and am asking for help because I think I may have thought myself into a corner. Another perspective would

be

valued.

TIA, Mike MacSween

If you're looking for another perspective - here's a different direction

to consider:

Do not rely on the table structure to provide the information you require - use a system of product codes and masking instead.

Here are some product codes you might use:

WT2004-XX-XX-XX-000 World Tour 2004
WT2004-US-XX-XX-000 US leg of 2004 World Tour
WT2004-US-NJ-HB-000 Hoboken Concert
WT2004-US-NJ-HB-001 Hoboken Concert - Dress rehearsal
WT2004-US-NJ-HB-002 Hoboken Concert - Show 1
WT2004-US-NJ-HB-003 Hoboken Concert - Show 2
WT2004-US-NJ-HB-101 Hoboken Concert - All shows incl. rehearsal

WT2004-EU-GE-XX-000 Germany
WT2004-EU-GE-HD-000 Heidelberg Concert
WT2004-EU-GE-HD-003 Heidelberg Concert - Show 2

This system allows you to code the products so you can retrieve the detail
you need. For example, find all the payments for the 2004 world tour - you just need WHERE ProductCode LIKE "WT2004*" or perhaps you need all years,
all concerts in Germany - WHERE ProductCode LIKE "??????-EU-GE-??-???"

I think you just need to accept there some things are too complicated to be handled by table structure alone and some form of meaningful analysis codes are needed. If you consider how a large supermarket might handle, say, a
specially priced multi-pack of fruit juice. For pricing reasons, they need a unique product code with a price, but for other reasons they may need to
know that they sold a pack with a total volume of 4 litres. The coding of
the product will let them analyse how much apple juice, orange juice, all
juices, how many multi-packs etc have been sold but no use will be made of
relational structures (eg you would not need tblMultiPack and tblItem).

I hope you are able to take this idea and apply it to your database - it
should make certain aspects a million times easier, more flexible and more
in line with how products are traditionally categorized.

</MyTuppenceWorth>

Fletcher

[Mike MacSween]

OK, still going at it and testing my ideas in public.

Seems to me that in the 'Element' table what's required is a 'Level' field.
1 being the top, 10 (for instance) being the bottom. With a few validation
rules. An element at level 1 can't have a parent, at level 10 can't have a
child. A child must have a level that is parent level+1.

That imposes a few restrictions. Children can't have more than one parent.
That's a requirements issue, I'm awaiting a response from the client. There
can't be more than 10 levels. Although the structure of the recursively
joined table _theoretically_ allows infinite levels, in this app that won't
be the case. It's perfectly possible to imagine saying to this client, or
the similar clients its aimed at 'look, you can't have a structure more than
10 levels deep'. Or 5 or 20. It would be in that range. Whereas with a
complex BOM there might be a far taller tree. The important thing in this
app is that it is variable. From a single event to 10 nested sub events.

The level number might make a lot of SQL easier. You'd know how many sub
queries to search from top to bottom, if the bottom was at level 4, for
instance. I'm guessing.

Actually I don't thing the nested sets BOM does it. It just models _one_
thing. That's not what I want. I need more than one root node.

Yours, Mike MacSween

[Fletcher Arnold]

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

OK, still going at it and testing my ideas in public.

Seems to me that in the 'Element' table what's required is a 'Level' field. 1 being the top, 10 (for instance) being the bottom. With a few validation
rules. An element at level 1 can't have a parent, at level 10 can't have a
child. A child must have a level that is parent level+1.

That imposes a few restrictions. Children can't have more than one parent.
That's a requirements issue, I'm awaiting a response from the client. There can't be more than 10 levels. Although the structure of the recursively
joined table _theoretically_ allows infinite levels, in this app that won't be the case. It's perfectly possible to imagine saying to this client, or
the similar clients its aimed at 'look, you can't have a structure more than 10 levels deep'. Or 5 or 20. It would be in that range. Whereas with a
complex BOM there might be a far taller tree. The important thing in this
app is that it is variable. From a single event to 10 nested sub events.

The level number might make a lot of SQL easier. You'd know how many sub
queries to search from top to bottom, if the bottom was at level 4, for
instance. I'm guessing.

Actually I don't thing the nested sets BOM does it. It just models _one_
thing. That's not what I want. I need more than one root node.

Yours, Mike MacSween

Well it won't take you long to design a table of elements: ElementID,
ElementName, ParentID, etc with each element having a parent in the table.
But before you go too far finalising the structure - consider the basic
issues of getting the data into the database and extracting it later. For
example, how does the user create a new production with different elements
and then link musicians and others to these elements? What will a print-out
look like? Do you intend to make use of some form of activex tree-control
or do you have another way to show this hierarchy on a piece of A4 paper?

I personally think that unless you have had a lot of experience with product
codes and fully appreciate the benefits of masking techniques with analysis
codes, then you should further investigate this before dismissing it. There
is a genuine reason why so many databases use this method - even if you use
a treeview control, even if your elements table has a parentID column, even
if you hide this code from the user and use VBA to build it up. Using
masking provides an extremely fast and efficient way to work with this sort
of data.

Alternatively, quickly build the elements table (the proposed design is
pretty straight forward) and then try to construct a couple of key forms and
reports around it. Let us know how you get on.

Fletcher

[Mike MacSween]

Fletcher

Thanks. I wasn't dismissing your codes out of hand. I appreciate the advice.
As a matter of fact right now I'm looking at materialised paths. This
http://fungus.teststation.com/~jon/t...eeHandling.htm in
particular looks interesting. But I repeat I've only just come across this.
Though your codes look a little bit like materialised paths (and that's a
very top of the head comment!).

Yes, I usually work the way you suggest. Come up with a fairly abstract
ER/CDM model (or one corner of it). Then quickly knock up some nonsense
tables in Access to see if/how well it works. Missing out the weeks of
correct relational modelling and such.

Cheers, Mike

[Roger]

nested sets do allow for multiple root nodes.. ie trees
if used it to create a small mdb per a 'communication' thread posted
here last week - I can send you a copy if you wish ?

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@news.aaisp.net.uk> ...

OK, still going at it and testing my ideas in public.

Seems to me that in the 'Element' table what's required is a 'Level' field.
1 being the top, 10 (for instance) being the bottom. With a few validation
rules. An element at level 1 can't have a parent, at level 10 can't have a
child. A child must have a level that is parent level+1.

That imposes a few restrictions. Children can't have more than one parent.
That's a requirements issue, I'm awaiting a response from the client. There
can't be more than 10 levels. Although the structure of the recursively
joined table _theoretically_ allows infinite levels, in this app that won't
be the case. It's perfectly possible to imagine saying to this client, or
the similar clients its aimed at 'look, you can't have a structure more than
10 levels deep'. Or 5 or 20. It would be in that range. Whereas with a
complex BOM there might be a far taller tree. The important thing in this
app is that it is variable. From a single event to 10 nested sub events.

The level number might make a lot of SQL easier. You'd know how many sub
queries to search from top to bottom, if the bottom was at level 4, for
instance. I'm guessing.

Actually I don't thing the nested sets BOM does it. It just models _one_
thing. That's not what I want. I need more than one root node.

Yours, Mike MacSween

[--CELKO--]

>> I've done a fair bit of research. Which frequently brings up the
words 'Joe Celko' and 'BOM'. <<

Now I am obligated to provide a nested sets solution! Let me be
sloppy and not put on all of the constraints for a tree:

CREATE TABLE Events
(event_name VARCHAR(35) NOT NULL,
performance_nbr INTEGER NOT NULL, -- or date?
performer_id INTEGER NOT NULL
REFERENCES Performers (performer_id)
ON UPDATE CASCADE,
fee DECIMAL(14,4) NOT NULL CHECK (fee >= 0.00),
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt),
PRIMARY KEY (event_name, performance_nbr, performer_id)
);

When you put in a node, put it in at the appropriate level in the
tree. An arranger shows up at the highest event level for all
performances (do they get paid by performance or by show? I don't know
this trade). A nose flute player shows up at the lowest levels like
"Elvis-2004 Tour; jail house rock number" and performances 1,2,3 and
7.

Now the regular hierarchical summations will work.

[Mike MacSween]

"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...

nested sets do allow for multiple root nodes.. ie trees
if used it to create a small mdb per a 'communication' thread posted
here last week - I can send you a copy if you wish ?

Yes please. What constitutes 'small'? I've cross posted here, can you
remember which NG and the subject line?

Yours, Mike MacSween

[Mike MacSween]

"--CELKO--" <jo*******@northface.edu> wrote in message
news:a2**************************@posting.google.c om...

I've done a fair bit of research. Which frequently brings up the

words 'Joe Celko' and 'BOM'. <<

Now I am obligated to provide a nested sets solution! Let me be
sloppy and not put on all of the constraints for a tree:

Ah, I know how to tease them out!
CREATE TABLE Events
(event_name VARCHAR(35) NOT NULL,
performance_nbr INTEGER NOT NULL, -- or date?
performer_id INTEGER NOT NULL
REFERENCES Performers (performer_id)
ON UPDATE CASCADE,
actually might not be, probably more like a junction table between musicians
and events
fee DECIMAL(14,4) NOT NULL CHECK (fee >= 0.00),
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE,
CHECK (lft < rgt),
PRIMARY KEY (event_name, performance_nbr, performer_id)
); When you put in a node, put it in at the appropriate level in the
tree. An arranger shows up at the highest event level for all
performances (do they get paid by performance or by show? I don't know
this trade).
Varies. Probably a 'buy-out'. We give you £2000 to write a set of
arrangements for this tour, or anything else we want to use them for in the
future.
A nose flute player shows up at the lowest levels like
"Elvis-2004 Tour; jail house rock number" and performances 1,2,3 and

It's actually getting quite hard to fix good nose flute players <g>

While you're there though. I want many trees in this forest. More than one
root. How's that done? Start each one off at lft = max(rgt) + 1 (+margin of
safety), rgt = lft+1?

Basically each tree will be relatively static. Certainly once a tour/series
of events has passed there should be no need to add events. The depth of
even the deepest tree wouldn't be much above 5 levels. And most (for this
client) would be 2 - 1 root and 8 ish leaves.

Yours, Mike MacSween

[Roger]

comp.databases.ms-access
I just emailed it to you (52k zip -> 200K mdb)

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@news.aaisp.net.uk> ...

"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...

nested sets do allow for multiple root nodes.. ie trees
if used it to create a small mdb per a 'communication' thread posted
here last week - I can send you a copy if you wish ?

Yes please. What constitutes 'small'? I've cross posted here, can you
remember which NG and the subject line?

Yours, Mike MacSween

[Joe Celko]

>> nested sets do allow for multiple root nodes.. ie trees
<<

Nope; by definition, a tree has one and only one root. But you can
create a forest of trees in a single table.

Oh, before I forget, the book on Trees & Hierarchies in SQL is in
production now and I think the April or May publication date is still
good.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike MacSween]

"Joe Celko" <jo*******@northface.edu> wrote in message
news:3f*********************@news.frii.net...

nested sets do allow for multiple root nodes.. ie trees

<<

Nope; by definition, a tree has one and only one root. But you can
create a forest of trees in a single table.

Yes, I guess that's what I mean. How?

Cheers, Mike

[--CELKO--]

>> I want many trees in this forest. More than one root. How's that
done? <<

You cannot, by definition, have more than one root in a tree. You can
have forest, tho:

CREATE TABLE Forest
(tree_id CHAR(5) NOT NULL,
node_id CHAR(5) NOT NULL REFERENCES Nodes(node_id),
lft INTEGER NOT NULL UNIQUE,
rgt INTEGER NOT NULL UNIQUE,
..);

Now you can set up rules about nodes and trees.

[Mike Sherrill]

On Fri, 2 Jan 2004 08:53:47 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show
I'd just observe that a world tour is probably a tour, not a job.
Whatever a job is. A tour isn't a leg. A leg isn't a job.
Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join.

What else did you imagine?

--
Mike Sherrill
Information Management Systems

[Mike MacSween]

"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:3g********************************@4ax.com...

On Fri, 2 Jan 2004 08:53:47 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

I'd just observe that a world tour is probably a tour, not a job.
Whatever a job is. A tour isn't a leg. A leg isn't a job.

As you aren't sure what a job is then I don't know how you can state that a
world tour isn't one.

It's perfectly clear what a job is. It's the name I've applied to an entity
type. An instance of which may be related to other instances of the same
entity type. This looks like the sort of thing that could be modelled using
some sort of hierachical structure. You seem to have other ideas. Perhaps
you could let me know what they are.

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join.

What else did you imagine?

What does that mean? I imagined what I said I imagined. A recursive join.

Since then I've found out about materialised paths and nested sets. Nested
intervals look useful:

http://dbazine.com/tropashko5.shtml

But I'm afraid my maths isn't up to understanding it fully.

Yours, Mike MacSween

[Mikito Harakiri]

"Mike MacSween" <mi******************@btinternet.com> wrote in message
news:3f***********************@news.aaisp.net.uk.. .

Since then I've found out about materialised paths and nested sets. Nested
intervals look useful:

http://dbazine.com/tropashko5.shtml

But I'm afraid my maths isn't up to understanding it fully.

If you have 5 or 10 levels, as you mentioned, you are safe. Otherwise, watch
out for numbers overflow.

Also, as you have not a single tree, but forest, so that you might be
tempted to model it like this:

..1
..1.1
..1.2
..2
..2.1
..2.1.1
..2.2
....

then watch out for overflow too, because, the sequence of root nodes with
paths
..1
..2
..3
....
is growing as
3/2
3/4
3/8
....

However, as Celko noticed you can easily identify tree's in a forest with a
dedicated ids.

BTW, in Nested Sets you can model forest as

[1,20] -- tree 1, root
[2,5] -- tree 1, 1st child
[5,10] -- tree 1, 2nd child
[11,19] -- tree 1, 3rd child
[21,100] -- tree 2, root
....

There is really no need for a dedicated tree id.

If updates are not a problem in your case, why don't you consider Nested
Sets?

[Roger]

Mike

after reading this thread and your 'hierachical thread', this is what
I've put together this model

now I've setup an 'event' to have a (parentEvent, childSeq), but it
could also be a nested set

thoughts ?
activity
activityId (pk)
activityName

ie. setup, teardown, dancing, stagehand, flute, drum, tourguide,
copyright payment
++++++++

contactType
contactTypeId (pk)
contactType

ie. musician, agency, company
+++++

contactInfo
contactInfoId (pk)
contactName
address
phone
fax
email
contactTypeId (fk)
parentContactInfoId (fk)

ie. bill, anne, ford modelling agency, acme company,joe
++++++++

provider
providerId (pk)
contactInfoId (fk)
unionId (fk)

ie. bill, anne, ford modelling agency, acme company
+++++

providerActivity
providerId (pk)
activityId (pk)

ie. bill-setup, bill-teardown, anne-drum, acme company-dancing
+++++++

union
unionId (pk)
unionName

ie. actra,
++++++

unionActivity
unionId (pk)
activityId (pk)
flatRate
hourlyRate

ie. actra-setup, actra-teardown, actra-musician
+++++

providerUnion
providerId (pk)
unionId (pk)

ie. bill-actra, anne-actra
++++++++

location
locationId (pk)
locationName

ie. little Johnny's house, MGM Grand
+++++

locationContact
locationId (pk)
contactInfoId (pk)

ie. Johnny's mother, Johnny's Father, MGM Grand general manager
++++++

event
eventId (pk)
eventName
startDateTime
endDateTime
contactInfoId (fk)
locationId (fk)
parentEventId (uk) (or nested set)
childSequence (uk)

ie. world tour, US leg, state tour, NewJersey appearance, dress
rehersal, first

show, Johnny's birthday party,
++++

eventActivity
eventId (pk)
eventSeq (pk)
activityId (fk)
providerCount

ie. world tour-scheduling, first show-dancing, first show-drum
++++

eventActivityStaffing
eventId (pk)
eventSeq (pk)
activityId (pk)
contactInfoId (fk)

ie. first show-dancing-joe (acme company employee), first
show-drum-anne
+++++

agreement
agreementId (pk)
providerId (fk)
eventId (fk)
agreementDate

ie. anne-first show, acme company-first show
++++

agreementDetail
agreementId (pk)
detailId (pk)
activityId (fk)
startDateTime
endDateTime
flatRate
hourlyRate
status (negotiation, booked, completed, paid)

ie. anne-first show-drum
++++

"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@news.aaisp.net.uk> ...

Regular viewers may want to turn off now.

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join. I've done a fair
bit of research. Which frequently brings up the words 'Joe Celko' and 'BOM'.
I'm not sure that the BOM is exactly what I need, but it's close. I actually
think an adjacency list is a better solution in this case than nested sets,
though probably implemented with (at least) 2 tables. This area may or may
not be the problem. As a matter of fact the same problems would arise, I
think, with a static structure, e.g:
Production-<Events

In the 'real world', the person I'm doing this for, a musician may be booked
for a show, will need to be booked for some/all of the events in that show,
but may be paid at the show level. i.e. they are booked for 7 shows, but are
paid a total of $548.34 for all shows together. Involved at a 'child' level
but paid at a 'parent' level. Here there seems to be duplication, we KNOW
they are involved with the parent if they are involved with any of the
children, and so on all the way up the hierachy.

Some people may be involved with a parent but NOT it's children. An arranger
writes the orchestrations for a week long show, but doesn't turn up on the
shows. So there is no certainty that people can only be involved at the
lowest child level.

But if on some occasions people are involved at a child level and no parent
level (paid by the individual show) whereas on others they are involved with
both (booked for the gigs, paid by the week) there's a difference, sometimes
there are duplicate data, sometimes there aren't.

OK, if it's just payment that's the problem, spin if off into 2 junction
tables:

Event -<Fee>-Musician

Event-<Booked>-Musician

That separates the two things that are getting muddled. But this now looks
strange. People are now getting paid for jobs they might not be involved in.
How so? It would actually accomodate copyright payments and suchlike (which
aren't part of the requirements) but it still looks strange. And I suspect
complex to implement.

I realise this isn't the first time I've asked for help on this, but I have
done a huge amount of pondering/studying and am asking for help because I
think I may have thought myself into a corner. Another perspective would be
valued.

TIA, Mike MacSween

[Roger]

interesting articles...
but I don't understand the math in the first article
he says

function child_numer
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN num*power(2, child)+3-power(2, child);
END;

function child_denom
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN den*power(2, child);
END;

select child_numer(3,2,3) || '/' ||
child_denom(3,2,3) from dual

19/16

but I get, using an evaluation order [power] -> [*/] -> [+-]
num*power(2, child)+3-power(2, child)
3*power(2,3)+3-power(2,3)
3*9+3-9
27+3-9
21

and
den*power(2, child)
2*power(2, 3)
2*9
18

so it should be 21/18 or my math wrong ?
"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@news.aaisp.net.uk> ...

"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:3g********************************@4ax.com...

On Fri, 2 Jan 2004 08:53:47 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

I'd just observe that a world tour is probably a tour, not a job.
Whatever a job is. A tour isn't a leg. A leg isn't a job.

As you aren't sure what a job is then I don't know how you can state that a
world tour isn't one.

It's perfectly clear what a job is. It's the name I've applied to an entity
type. An instance of which may be related to other instances of the same
entity type. This looks like the sort of thing that could be modelled using
some sort of hierachical structure. You seem to have other ideas. Perhaps
you could let me know what they are.

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join.

What else did you imagine?

What does that mean? I imagined what I said I imagined. A recursive join.

Since then I've found out about materialised paths and nested sets. Nested
intervals look useful:

http://dbazine.com/tropashko5.shtml

But I'm afraid my maths isn't up to understanding it fully.

Yours, Mike MacSween

[Mikito Harakiri]

"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...

interesting articles...
but I don't understand the math in the first article
he says

function child_numer
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN num*power(2, child)+3-power(2, child);
END;

function child_denom
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN den*power(2, child);
END;

select child_numer(3,2,3) || '/' ||
child_denom(3,2,3) from dual

19/16

but I get, using an evaluation order [power] -> [*/] -> [+-]
num*power(2, child)+3-power(2, child)
3*power(2,3)+3-power(2,3)
3*9+3-9
27+3-9
21

and
den*power(2, child)
2*power(2, 3)
2*9
18

power(2,3) = 8 != 9

Do different vendors implement power differently???

[Roger]

oops.. I was thinking three-squared, not two-cubed... thanks

"Mikito Harakiri" <mi*********@iahu.com> wrote in message news:<ux*************@news.oracle.com>...

"Roger" <le*********@natpro.com> wrote in message
news:8c**************************@posting.google.c om...

interesting articles...
but I don't understand the math in the first article
he says

function child_numer
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN num*power(2, child)+3-power(2, child);
END;

function child_denom
( num integer, den integer, child integer )
RETURN integer IS
BEGIN
RETURN den*power(2, child);
END;

select child_numer(3,2,3) || '/' ||
child_denom(3,2,3) from dual

19/16

but I get, using an evaluation order [power] -> [*/] -> [+-]
num*power(2, child)+3-power(2, child)
3*power(2,3)+3-power(2,3)
3*9+3-9
27+3-9
21

and
den*power(2, child)
2*power(2, 3)
2*9
18

power(2,3) = 8 != 9

Do different vendors implement power differently???

[Mike Sherrill]

On Tue, 6 Jan 2004 00:19:46 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

>This will be an orchestral management system. Musicians and other staff
>being booked/paid for jobs.
>
>A job may contain other jobs, e.g:
>
>World Tour contains
>US leg and Europe leg (and others)
>US leg contains State tours (and others)
>New Jersey tour contains Hoboken concert (and others)
>Hoboken concert contains dress rehearsal, 1st show, 2nd show
I'd just observe that a world tour is probably a tour, not a job.
Whatever a job is. A tour isn't a leg. A leg isn't a job.

As you aren't sure what a job is then I don't know how you can state that a
world tour isn't one.

The fact you speak of these things using different names for them is
one clue.

If a world tour were a job, and a leg were a job, and a state tour
were a job, and a concert were a job, and a dress rehearsal were a
job, and a show were a job, everything would be a job. That's a
second clue--it's relatively rare for "everything" to be a "whatever
you like".

But if everything really is a job in the real world, you either have
only *one* kind of thingy--a job--or job is a supertype, and tours,
legs, and so on are its subtypes.

A third possibility is that "job" might not be anything at all--just a
shorthand way of talking about things rather than a thing itself.

You can tell which one of these three apply by ferreting out the
attributes of a tour, a leg, a rehearsal, and so on. Then reconcile
the attributes.
It's perfectly clear what a job is.
It's not clear to me. A world tour seems to have different attributes
than a dress rehearsal.
This looks like the sort of thing that could be modelled using
some sort of hierachical structure.
That's possible. But there are other structures, and "hierarchical"
doesn't imply "recursive".
You seem to have other ideas. Perhaps
you could let me know what they are.

Imagine the simplest thing that does something useful. That exercise
will often steer you toward the essential feature.

"Fred Flintstone asks Barney Rubble
to perform
at the Bedrock City Hall
on 06-Jul-2004 at 7:00 pm."

Tours, legs, and such seem like convenient ways to aggregate
performances. But, as convenient as they might be, they don't seem
essential to me. That's another clue.

>To account for the variability I imagined a recursive join.

What else did you imagine?

What does that mean? I imagined what I said I imagined. A recursive join.

I mean, did you imagine anything besides a recursive join? What else
did you try? For example, did you model a "tour" or "performance" (or
"cancelled performance")?

--
Mike Sherrill
Information Management Systems

[Mike MacSween]

"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:8c********************************@4ax.com...

Some very pertinent points there Mike. Thanks for prompting me to thing
about this more rigourously.

The fact you speak of these things using different names for them is
one clue.
Perhaps. The names I used are the sorts of names, descriptive labels, that
users might apply to them. Whatever the things are.
If a world tour were a job, and a leg were a job, and a state tour
were a job, and a concert were a job, and a dress rehearsal were a
job, and a show were a job, everything would be a job. That's a
second clue--it's relatively rare for "everything" to be a "whatever
you like".
That's very true. There are other things in this database, like musicians
and instruments.
But if everything really is a job in the real world, you either have
only *one* kind of thingy--a job--or job is a supertype, and tours,
legs, and so on are its subtypes.
Possibly.
A third possibility is that "job" might not be anything at all--just a
shorthand way of talking about things rather than a thing itself.
Possibly.
You can tell which one of these three apply by ferreting out the
attributes of a tour, a leg, a rehearsal, and so on. Then reconcile
the attributes.
OK. What they are then, as it looks at the moment, are groups. Things can be
grouped together, and the groups can be grouped. Pretty much like
assembly/sub-assembly:

As pretty much every component on my car has packed in this summer lets say
the engine goes next. I go to my Ford dealer and ask him for:

1 Crankshaft
4 Con rods
8 big end bearing shells
etc. etc.

After 3 hours he says - 'so you want an engine then?'

This isn't a facile example. The engine has child elements and can be a
child. You might argue that the engine has it's own attributes (like
capacity) that only come into existence once it is assembled.

But mainly the 'engine' thing is a collection of parts, and a very common
way of referring to it. And it can be treated as a discrete component.

My groups are the same. Although the European leg will, at the lowest level
be made up of things which we can call 'events' - rehearsals, performances
and such, it will still be able to be handled as an entity on it's own.
Certain staff will be employed just for that thing (the European leg), but
perhaps not for the 'events' that make it up.

There is no way atall to predict how various users will want to divide up
the events into groups and super groups, so I will make no attempt to come
up with any sort of fixed table structure - Tours, Legs or whatever.

I agree, the fact that an entity type might only have the attributes Name,
Parent (joint primary key) may look odd. But believe me, these users think
very much about things like 'The Hamburg Week' or whatever. To them the
collection of events called The Hamburg Week is just as real as that
collection of things which we all accept is called an 'engine'.

It's perfectly clear what a job is.

It's not clear to me. A world tour seems to have different attributes
than a dress rehearsal.

Yes. A dress rehearsal has no sub events. In this model.

This looks like the sort of thing that could be modelled using
some sort of hierachical structure.
That's possible. But there are other structures, and "hierarchical"
doesn't imply "recursive".

Yes, you're right

You seem to have other ideas. Perhaps
you could let me know what they are.

Imagine the simplest thing that does something useful. That exercise
will often steer you toward the essential feature.

"Fred Flintstone asks Barney Rubble
to perform
at the Bedrock City Hall
on 06-Jul-2004 at 7:00 pm."

Tours, legs, and such seem like convenient ways to aggregate
performances. But, as convenient as they might be, they don't seem
essential to me.

Very convenient. Also known as essential.

Let's say I 'fix' for Cameron MacIntosh (big West End producer). What do I
have? A list of performances:

Perf1, Theatre Royal Drury Lane, 13/9/2003, 7.30, 10.00, West Side Story
Perf2, Leeds Grand Theatre, 13/9/2003, 7.30, 10.00, West Side Story

We can see what there is in common about these things, and also what is
different. How will Mr MacInstosh's staff refer to these two shows (and the
hundreds of others)? One is part of the West End Production of West Side
Story and the other is part of the Touring Production of West Side Story.

>To account for the variability I imagined a recursive join.

What else did you imagine?

What does that mean? I imagined what I said I imagined. A recursive join.

I mean, did you imagine anything besides a recursive join? What else
did you try? For example, did you model a "tour" or "performance" (or
"cancelled performance")?

One model might be this:

Production (can't have a parent)
one to many
Groups (recursive?) - a sub type of Production?
one to many
Events (can't have children)

Where group and event may well be sub or super types of the same entity.

Mike MacSween

[Roger]

I've created an access97 d/b implementing the theory in this link that
Mike has provided... but I don't have a site to distribute to people

I can email a copy to someone who has a site that people can then
access
"Mike MacSween" <mi******************@btinternet.com> wrote in message news:<3f***********************@news.aaisp.net.uk> ...

"Mike Sherrill" <MS*************@compuserve.com> wrote in message
news:3g********************************@4ax.com...

On Fri, 2 Jan 2004 08:53:47 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

This will be an orchestral management system. Musicians and other staff
being booked/paid for jobs.

A job may contain other jobs, e.g:

World Tour contains
US leg and Europe leg (and others)
US leg contains State tours (and others)
New Jersey tour contains Hoboken concert (and others)
Hoboken concert contains dress rehearsal, 1st show, 2nd show

I'd just observe that a world tour is probably a tour, not a job.
Whatever a job is. A tour isn't a leg. A leg isn't a job.

As you aren't sure what a job is then I don't know how you can state that a
world tour isn't one.

It's perfectly clear what a job is. It's the name I've applied to an entity
type. An instance of which may be related to other instances of the same
entity type. This looks like the sort of thing that could be modelled using
some sort of hierachical structure. You seem to have other ideas. Perhaps
you could let me know what they are.

Or a job may be single:

My band plays at Simon Foreman's barmitzvah

To account for the variability I imagined a recursive join.

What else did you imagine?

What does that mean? I imagined what I said I imagined. A recursive join.

Since then I've found out about materialised paths and nested sets. Nested
intervals look useful:

http://dbazine.com/tropashko5.shtml

But I'm afraid my maths isn't up to understanding it fully.

Yours, Mike MacSween

[Mike Sherrill]

On Wed, 7 Jan 2004 21:42:03 -0000, "Mike MacSween"
<mi******************@btinternet.com> wrote:

Tours, legs, and such seem like convenient ways to aggregate
performances. But, as convenient as they might be, they don't seem
essential to me.

Very convenient. Also known as essential.

They're not essential attributes of a booking (or engagement, or
performance). I toured the US as a sound engineer. We didn't have a
Northern leg or a New York leg. That's one clue.

That every set of users might have a different way of talking about
groups of performances is another clue.

You'd likely profit from separating the essential attributes of a
booking from the admittedly arbitrary way people will talk about
groups of bookings.

--
Mike Sherrill
Information Management Systems