I have tried to find an answer to this but haven't been able to yet. I have a scenario in which I have 4 tables. (I'm attempting to simplify things)
People
Activities
Risks
Precautions
These are chained together in multiple many-to-many relationships. For example,
A person can do multiple sports, and multiple people can do the same sport. A sport can have multiple risks, and multiple sports can have the same risk. A risk can have multiple precautions, and multiple risks can have the same precaution.
I see 3 ways to do this with junction tables...
1.
People ---------
|- Junction Table
Activities -----
|- Junction Table
Risks ----------
|- Junction Table
Precautions ----
2.
People ------------------------------------------------
|- Junction Table
Activities ------------------------ |
|- Junction Table --
Risks ---------- |
|- Junction Table --
Precautions ----
3.
People ------------|
Activities --------|-- 1 big junction table
Risks -------------|
Precautions -------|
This is where I'm stuck. I'm not sure what to do or if I'm not even thinking about it correctly to begin with. I feel like option 1 is the way to go but am then unsure how to query from one end to the other. For example...from a person, select precautions tied to them.
I would go with option 1 and do join queries. For the example query: from a person, select precautions tied to them
you should get the person's activities from join table Person and Activities on person id and then join that with risks on activities and finally join that with precautions on risks. In the end, you will get the precautions associated with a person.