Multiple many-to-many relationships with hierarchical data

[PhonLing]

I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.

tblLangInfo: LangID (PK), language name, language family, etc.

tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in

tblSegmentLangJoin: LangID (PK), SegmentID(PK)

I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:

tblProcesses: ProcessID (PK), Process name

tblProcessLangJoin: LangID (PK), ProcessID (PK)

Here's the fun part...

I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:

Process1_triggers
Process1_targets
Process2_triggers
...

This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.

The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.

E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.

I've attached a picture to illustrate the relationship I need, since that's likely clearer.

One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table, but I haven't figured out how to do this and make sure I don't have duplicates for language-segments pairs. Perhaps there's an easy solution, but my googling skills haven't proved up to the task on this one.

Any insight as to how to appropriately define my data tables and relationships would be much appreciated.

 

[MarkK]

If I understand correctly you want a many-to-many join between a 1) language dependent segment to a 2) language dependent process. To do so you would create a new join table, and join your current join tables tblSegmentLangJoin to tblProcessLangJoin via that new join table. That was you can join your english segments and english processes, which themselves, it so happens, also function as join tables.

Don't get distracted by the thought that a join table is somehow too abstract to use as a parent or master table. A join table defines a relationship between objects, yes, but once it does so, the thing it defines is also an object.

 

[PhonLing]

Thank you for replying.

Almost. I think perhaps I wasn't entirely clear. It's true that languages are related to processes, and that I want to relate the language dependent segment to a language dependent process. However, I specifically want to know whether said segment is a trigger or target of the process, which is why I was going to have a separate table with "process parts" (for lack of a better name). It's more like, "Does this language have Process A? Does this language have Segment X? Is X a trigger, target or neither of Process A (in this language)?" This is the way I think about my data, but perhaps there's a better way to conceptualize the trigger/target nature of the process.

Regardless, if I understand you correctly, it seems the solution is to create a new join table between tblSegmentLangJoin to another new join table tblProcessPartsLangJoin. It seems a bit messy (it would be nice to have the process parts and processes related more explicitly). Is this what you would still suggest?

Again, thanks for your time.

 

[PhonLing]

Scratch that. I think I've figured out what you meant and what is the appropriate way to go about this.

I have the following base tables:

tblLangInfo: LangID (PK)

tblProcesses: ProcessID (PK)

tblSegments: SegmentID (PK)

I have the following join tables:

tblLangSegmentJoin: LangSegmentID (PK) with unique composite index for the other two fields, LangID, SegmentID

tblLangProcessJoin: LangProcessID (PK) with unique composite index for the other two fields, LangID and ProcessID

A new join table (I believe this is what you meant), tblLangSegProcJoin, with a composite primary key of LangSegmentID and LangProcessID (joined to the respective join tables above), and a third field "Trigger/Target", in which the values "trigger; target; both; neither" can be selected.

Thank you very kindly for your help!