How do I make a table that can have an endless chain of children?

[Rapt Productions]

I'm trying to create a table, "type." From "type," I want to be able to add table upon table - i.e. one type would be "people," and "people" would then allow for "clients," "personnel," "vendors," then "personnel" might have "editor," "cameraman," "admin," and so forth. What is the cleanest way to create this branching structure and allow users to define additional types (without creating redundant ones?) Thanks!

[Fenton]

Just create a "reference" table for each "level". Every child must have a parent.* In your main table, where you're labeling something (people?),** you would need a field for each level. Or a join table (or just multi-line field) if anyone could have more than one entry from any one of the levels.

You would need as many fields as levels. So it is not "endless." Or, you could just store the last value, the others being implied. But we don't really know if that would be adequate for what you're using this for; it wouldn't work very well with IDs.

*Each table must have a parent value, which would match its parent table; either a name, or preferably, ID. Because you're going to filter choices, via relational value lists.

** I don't see what the "entity" of your main table is. I don't see why you would mix "people" with anything else in a table; it's kind of the top of any hierarchy I can think of, other than a collection of animals.

[comment]

If I understand this correctly, you want a single table of Types, where each record CAN have a parent type. The basic structure would be:

TypeID

Type

ParentTypeID

and you would define two self-join relationships:

Type::TypeID = ChildTypes::ParentID

Type::ParentID = ParentType::TypeID

For each type, you select the immediate parent type by placing its ID into the ParentTypeID field. The two relationships enable each record to "see" its parent and its children.

[Rapt Productions]

Yes, I think that's it - a single table in which each record can have a parent type. I'm not sure I understand how to set it up, though - you mention three fields, TypeID, Type, and ParentTypeID, but then where does ChildTypes come into the picture? Thanks so much for the help!

[comment]

For each record, its children are those that have this record's ID in their ParentID field. They can be seen in a portal based on the first relationship above.

[Rapt Productions]

Are ChildTypes and ParentType the other occurrences of Type? And is ParentID the same as ParentTypeID? I apologize if I'm being dense, thanks again for the help.

[comment]

Yes to both.

[Rapt Productions]

I'm sorry, but I'm still lost. How do I set this up in a layout or a value list? I created a portal in the layout, but I can't understand the results I'm getting...

[comment]

Perhaps you should look at these:

http://fmforums.com/forum/showpost.php?post/243991/

http://fmforums.com/forum/showtopic.php?tid/135595/

However, be advised that recursive cascading relationships are not exactly beginners stuff - be prepared to spend some time with this.

[Rapt Productions]

Well, at least I can take a little comfort in knowing that as a beginner, I should be having as hard a time with this as I am! These look like what I'm attempting to do - I'll try and figure them out. Thanks comment for your time and help!