This might be more of an SQL problem than a CakePHP problem, but anyway. Say I have the following tables:
These are all associated in various ways that do not really matter. Then there is an additional table:
Publications can be about one or more place, one or more time periods, one or more designers, one or more materials, one or more items, or multiple of the above. To associate these, I see three options:
- Create regular join tables for each of the tables that can be linked to
- Create a single join table with columns like
other_table_nameand make conditions with
other_table_namein the conditions.
- Create a table
topicswith just an
idcolumn, and add
materialsetc. Then, create a regular join table to link
I would really like to avoid option (1) because in reality I have more tables that can be linked to
publications than in this example, and I have more tables like
publications that can be linked to those same tables. I dislike option (2) because it seems very un-idiomatic. Option (3) is more idiomatic and works fine in the
publications direction but in the other direction it faces the same problem that option (2) tries to solve (i.e. not knowing which table to query). What’s the best option here?