Re: Hierachical structures - an overview

"Lyle Fairfield" <MissingAddress_at_Invalid.Com> wrote in message news:Xns9466425916D4CFFDBA_at_130.133.1.4...

> A Google Search for "Hierarchical Databases" brings up several thousand
> (3860) references. The first twenty are somewhat similar, but scarcely
> definitive in any helpful (to me) way. Generally they are described as
> being like trees, or directory structures, or "Information Management
> Systems" of the past.

Thanks Lyle. Yes, I've done Google searches, and come up with the 3 approaches I've quoted. That's the reason for this post.

> One points out a common problem: a child with more
> than one parent.

Yes, and I've just established from the client that that isn't a requirement. Thank god.

> I think you have been descriptive of your needs in another thread. But
> perhaps, in this one, you could delineate a particular problem or
situation
> dealing effectively with which requires specific design consideration.

Sure. But I'll briefly re-iterate for the benefit of anyone who hasn't seen those threads. It's an orchestral management system. The area of the schema that I think needs to be hierachical is the Tour-Concerts section. Like this:

World Tour has
Regional sections has
Country tours has
State tours has
City concerts has
Rehearsal, afternoon concert, evening concert.

But the hierachy could range from that (5-10 levels would do it, I think) to a simple string quartet playing at a wedding - 1 level.

People (musicians) can be booked in at any level. An arranger (writes music) for the whole tour, but doesn't need to turn up at anything else. Players need to be booked in at the lowest level, the concert. And any level in between.

Potential problem: Let's say I use an adjacency list as a structure. When the client calls me, she says - 'Can I book you for Carousel next year Mike, not sure of the dates but its 19-26 of October'. I say OK. All she has so far is a single event, lasting a week. So once I'm booked on it I'm a record in a junction table between the Events table and the Musicians table. Then she finds out the dates. She enters them, as 'sub-events' of the week long event. Children of the parent. No doubt I have some event handler that says 'you are entering dates for this show, do you want everyone so far booked onto the show to be booked onto each event' or some such. She clicks yes. There are now 2 entries (probably 8, one for the week, one for each night) in that junction table. At a relational level it looks OK. But at a 'logical' level I'm entered twice for the same 'thing'. There should be no need for me to be entered for both the parent and the child. If I'm entered for the child it's obvious I'm entered for the parent too. Update query needed? Probably. At which point the difficulty of querying up the tree using standard SQL in an adjacency list might be a problem. There's more. What if she deletes every single sub event when she realises she's got it wrong. She'll have to re-enter the musos onto the parent event. But maybe rare enough not to worry about.

Cheers, Mike Received on Sun Jan 04 2004 - 17:52:40 CET