Table Relationship - One to Few (Many)

Question

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?

Answer 1

The compound PK of EmployeeId and AddressTypeId is exactly what you want, and you don't need anything else. The relationship is still a one-to-many relationship, as for one employee, you may have many address whether it is a home address or a work address.

The bridge table is interesting if you may have the same address for multiple employees AND that a same address may be of a different type for multiple employees. If it's not the case, this table is pretty overkill and may add more complexity for nothing.

TL:DR: First solution is probably the best.

Answer 2

I think it's over-prescriptive to say which of your two solutions is better. All you need is a unique constraint on {EmployeeID, AddressID}. Whether or not it's the actual primary key is up to you. It's perfectly fine to have a different primary key; single-field PKs do tend to work better with some ORMs, and there are benefits in simplicity to having all your PKs be predictable.

Whether you want to use a "bridge table" is independent of the problem of the unique constraint. You could, for example, use the bridge table with a single surrogate primary key (if your ORM needed it) and a unique constraint on {EmployeeID, AddressID}. Since you brought it up, it's worth talking about the reason you might want to use one.

The bridge table offers a significant benefit if your database is responsible for much more than just Employees & Addresses, or if your goal is high reusability: the concept of "Address" is completely independent of the concept of "Employees". Are there other database entities that might have an address? A Facility? A Customer? The first solution would handle this poorly because an Address cannot exist without being associated to an Employee. In the second solution, you just add more bridge tables.

tl;dr: unique constraint. Everything else is related to other concerns.

Answer 3

I think the 3rd ERD is better. However, I also have suppliers, contractors, and job applicants addresses, so I am wondering what the best approach would be to fit in the whole puzzle.