SQL rookie question about Join tables

I’m coming from the Filemaker world (and bringing my data with me :-)). I’m creating an iOS app with an embedded SQLite DB. In FM I have a (primarily) one Join table that joins multiple other tables. Is this advisable with SQL, or should I be doing multiple join tables (one for each unique join relationship)? For example:

Tables A, B, and C each get joined to Tables D and E. I use UUIDs for all primary keys. Is there any problem or potential pitfalls if I create Table X (join table) with columns like either?:

  1. id, id_A, id_B, id_C, id_D, and id_E
    or
  2. id, parentTableName, id_Parent, childTableName, id_Child

I prefer the second approach since I don’t like the “feel” of having a bunch of empty columns on every record. Also my actual data structure is quite abit more involved than 2 tables joined to 3, so the second approach feels simpler and more scalable. I’m particularly interested in how either of these approaches would affect displaying related records.

Are these one-to-one, one-to-many, or many-to-many relations?

These are many-to-many.
Do I need a join table for one-to-many? I’m used to simply having a foreign key field in the “child” table that links to the primary key in the “parent” table…

A lot will depend on you exact intent, which is hard to determine from the provided information.
SQLite is quite powerful, and capable of doing all type of joins, So again, more information about the desired result would be helpful

Ok, I’ll give an example of the kind of thing I wanna do…
My app has to do with language acquisition/documentation/analysis in minority languages (where there is little or no existing data).
I have (among many others) the following Tables: Words, Concepts, Sentences, Audio

Words (think foreign language) have a many-to-many relationship with Concepts (think e.g. English). Words also have a many-to-many relationship with Sentences. Audio has a m2m relationship with both Words and Sentences.
Now as the user flips through the Word records, all related Concepts should be displayed in a table (FM portal). Related Sentences will be displayed in another table. At the same time (the primary) Audio for both Words and Sentences will be indicated (playable).

Given this kind of Join Table I might have records in one table that look like:
ID, Words, UUID, Audio, UUID
ID, Words, UUID, Concepts, UUID
ID, Concepts, UUID, Sentences, UUID
ID, Sentences, UUID, Audio, UUID
etc

So my question is: with only the 4 scenarios I indicate, would I be better off with 1 join table or 4 (keep in mind that there are many more scenarios)?

the “join” should be the minimum required to satisfiy the requirement
and if the JOIN is by the PK value only, then the order of the tables in the join do not matter

If the table order DID matter, then you would have 24 scenarios, but if not you only have 1

So to clarify… the user types in a WORD, and it returns all the CONCEPTS -AND- SENTENCES -AND- AUDIO?
or do they type in a WORD, and select which of the 3 categories to return?
If the first, you have one massive join (and need to be careful if any sub table does NOT contain a matching key)
If the second, then you have 3 simple joins based on what category they choose.

If it is more complex, in which they can choose ANY category, and return ANY/ALL of the other categories then I might go with a single join with an appropriate WHERE clause to limit the returned values

Yes, and this is actually somewhat simplified… the flow is:
A Word record loads, it’s related Audio loads, it’s related Concepts load, a related Image for each Concept loads. All related Sentences for each Concept load, then each Sentence’s related audio and image load (all of this potentially on a single view). There are other relationships, but these are the ones that kinda need to fire automatically and “instantly”! In this workflow it’s “word-centric”, but there will be views where the other categories will be the “driver”, especially as I get into the data analysis part of the app. So given this (and greater) complexity, are there any reasons to stay away from a single (generic) join table? From what I’m hearing, it seems fine, but want to clarify that I’m seeing things right.

If speed (“instant”) is your goal, then DON’T built one massive query, even it it word centric… Why? because I’ll bet there will be situations where one or more matches don’t exist (you might not have the audio for a particular sentence etc). While the query can be built to compensate (return NULL). it needs to do more work

So one approach might be threads.

  • User enters a “word”
  • thread #1 fires "SELECT * from concepts “for that word”
  • thread #2 looks up sentences
  • and when #2 is done it looks up audio (since I assume the audio is actually related to the sentence not directly to the word)

This allows each query to be “simple”, yet all return as quickly as possible