Avoiding extra join tables when many tables are associated with a single table

Need Help
1

This might be more of an SQL problem than a CakePHP problem, but anyway. Say I have the following tables:

  • fashion_items
  • materials
  • designers
  • places
  • time_periods
  • etc.

These are all associated in various ways that do not really matter. Then there is an additional table:

  • publications

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:

  1. Create regular join tables for each of the tables that can be linked to publications, so fashion_items, materials, etc.
  2. Create a single join table with columns like publication_id, other_id, other_table_name and make conditions with other_table_name in the conditions.
  3. Create a table topics with just an id column, and add topic_id columns to fashion_items, materials etc. Then, create a regular join table to link topics to publications.

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 fashion_items → 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?

2

I just looked up table inheritance and I see another option:

  1. Create a sparse table topics with id but also fashion_item_id, material_id, etc. Most of these will be NULL but that means topic_id can be omitted (meaning those tables are simpler) and querying works well in both directions. And with the right settings the NULLs should not be a problem I think.
3

I’ve generally gone with your option 2, though I call the columns “foreign_id” and “foreign_model” (model being a bit more flexible than table name, in that you can potentially have a single table underpinning multiple models, and this allows that distinction to be maintained).

1 Like
4

I like the option 3, I used before an approach like this when each entity could be “assigned” some items (and keep track of those).

However I don’t see this problem

works fine in the fashion_items → 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)

If you “view” a publication you should list all fashion_items, materials, etc.
Unless one of them would be the “primary”/most important of the list and for that it would require the join approach.

1 Like
5

If you “view” a publication you should list all fashion_items , materials , etc.

That’s fair, maybe that’s fine.