Comment by tda
7 years ago
If I understand correctly you are describing a one to one or one to many relationship. The canonical way to express that is 'don't use a mapping table'. Mapping tables are for many to many relationships. So why is there a mapping table in you example, and could you just get rid of it altogether?
In this case it's tying together data from different, independent systems. So the schema may not be textbook ideal but I don't think there's any way around it.
If you really must use a mapping table, I think you could put a unique constraint on both of the foreign key columns too the mapped tables. That would enforce that every row in one table maps to at most one row in the other
I don’t think that fundamentally changes the nature of a one to many relationship though. Unless your mapping table has a variety of different columns to join on.
Can you use views or other feature to tie together a table and its mapping table into a single view?