I have a database that has a lot of many to many relationships.
They almost all consist of ID1, ID1, and then maybe one text field describing the relationship (not counting the uniqueID of the connection table, creation date, name, etc.)
What I'm wondering is if I should use different TOs of one connection table to make all (or most) of my many to many relationships or if I really should make seperate connection tables for each pair of tables I want to join.
For example, A meeting can have many organizers, and an organizer can organize many meetings. Meetings also have Associates in my company who are dealing with the meeting, a each associate can be dealing with many meetings. Both are many to many:
While I've been designing a new database I've found at least 4 many to many relationships that would use identical tables, and expect that more will pop up.
the IDs for my records should be unique throughout all tables, so in my mind using the same connection table for both joins should work, but I wondered if anyone had any experience with this. Besides the risk of my messing up my IDs and two records from two different tables sharing the same ID, are there any other risks I might be running? Any other gotchas? Best Practices?