I know the title may seem confusing but I have yet to find a solid answer to this design pattern. Let's say I want to create an Employee table and have multiple addresses for each employee. So this would call for your standard one-to-many relationship. You obviously want and Address Type column to track what type of addresses you are storing for each employee.
Now for my issue, I want to only allow one address per address type for each employee. This breaks the one-to-many model because I want to force this restriction at the database level. I see a few ways of dealing with this issue but I am stuck on which one is best based on best practices and functionality.
Below are the same tables with the AddressId removed, for lack of usefulness, and instead a compound primary key of EmployeeId and AddressTypeId. This would enforce one address type per employee, allowing each employee to have multiple addresses but only one of each type.
For the purposes of enforcing key restraints on the address table, this would probably work fine. But this seems more like a one-to-one/none relationship which could cause me issues with ORM that don't work with this kind of table design. (Correct me if I am wrong please.)
Below is what I know to be the typical bridge table (many-to-many-to-many) relationship. It, like the above table, deals with the address type restriction I want to enforce while also keeping each table well structured and with its own singular primary key. This design will work fine with most ORMs and is probably the correct way to go but it seems like overkill and it seems to break the meaning of the many-to-many relationship as there will never be an address with multiple employees.
Is the bridge table relationship design the best to be using despite its complexity? Other than the issues that might arise with an ORM would the compound key relationship cause issues I am not seeing? Or am I totally insane and seeing this all the wrong way and there is actually a better way to be dealing with this scenario?