Grandparent Relationship?

[chikanyc]

Hi,

I'm having trouble figuring out grandparent relationships--if that's at all possible.

I have 3 Tables:

(1) Episode table

(2) Segment table

(3) Line Item for Segment table

An episode can have multiple segments, and a Segment can have multiple line items.

Ideally, I would have a layout based on Episode. I'd create a new Episode Record and then create a new Segment record via a Portal. I'd like to add a SECOND portal displaying and adding line items for the selected Segment. Is this possible to do one one layout?

Confused.

Thanks:)

[ejpvi]

I may not understand your issue totally, but I have found in a situation where I have 2 tables, One Item in each, but can have multiple links, is to use a join table. Use Serialization in the two tables for their primary keys, and populate the join table using those foreign keys.

I have found that when I have many of these linked together, you can cross many tables for record creation as long as you have common variables to relate to, typically keys. This creates what you would call a many to many relationship, even though you may have only one record for each primary table.

Not sure if that helps, but I would look up "join tables." I found them very useful when my databases get complicated.

Like I said, not sure how you are creating records, but I pictured something like.

Episode table -> Join Table -> Segment table -> Join Table -> Line Item for Segment table

Hope that helps.

[Søren Dyhr]

My take here is that something has gone wrong in your normalization when arriving at a grandparental structure, shouldn't it have been a recursive structure instead?

http://jonathanstark.com/recursive_data_structures.php

...I mean that you couldn't have asked yourself thoroughly if any attributes from either one of you chosen tables has something near or directly in common!

--sd

[mr_vodka]

You can have the user select from the child list via your portal and set a global field with the ID. Then from that global field, your grandchild relationship would be keyed from this global to the foreign key there.

[chikanyc]

i've been trying to wrap my head around the link that soren posted and im so confused:(

i will try to re-describe the problem that i'm having.

EPISODE table

SEGMENT table

LINE ITEM for segment table

SONGS table

Every Episode can have multiple segments. A segment record will have multiple line items for the songs that are used in the segment.

Up until now, my database has not been using an Episode table--I am trying to implement the table now, but am having trouble. Before we would create a new record in the segment table/layout. There was a portal to the Line Items where we could select what songs were used.

Now, I would like to create a new record in the Episode table and via a portal create a segment record. And then from a portal within the segment portal, start entering Line Items records. Is this very hard to do? Maybe I should find a different way to organize my data?

[Søren Dyhr]

Maybe I should find a different way to organize my data?

What I in essence meant with my reply, beware that methods exist to put things on rails:

http://books.google.com/books?id=dkxsjXNayHQC&dq=mere+mortals+database&printsec=frontcover&source=bl&ots=eS64jTYbDZ&sig=BAhuLxAMniPhLI4iY7r5PgSiay8&hl=da&ei=VHpCSrzVAYjm-Qb-6ZzOCA&sa=X&oi=book_result&ct=result&resnum=3

--sd

[efen]

See if the attached helps

episodes.zip

[chikanyc]

Thanks for the reading list, Soren

Efen, I have something a little similar to that. But I want to be able to create Line Items for a particular Segment within the Episodes layout.

So in Episodes layout, create Segment records,AND for each Segment record, create multiple Line Item records...

Sorry if thisis confusing...I'm confused myself:(

[efen]

You can create segments and line items for the segments from this episode layout

[comment]

in Episodes layout, create Segment records,AND for each Segment record, create multiple Line Item records...

Try something like the attached, maybe?

NavPortal3.fp7.zip

[chikanyc]

thank you everyone for helping! efen and comment--your solutions worked great!

i love this forum and all the experts on it! you guys rock:)