5

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.

enter image description here

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.

enter image description here

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.

enter image description here

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?

  • 1
    You say that the 2nd design "deals with the address type restriction I want to enforce". But it doesn't. It allows more than one addresses of same type for the same employee. – ypercubeᵀᴹ May 3 '16 at 15:57
  • @ypercubeᵀᴹ You are correct, I see I put the key as a triple compound when it should have only been a compound of EmployeeId and AddressTypeId. That was a mistake on my hastily build diagrams. lol Had I designed it correctly it would have enforced the desired goal. – Nicholas May 3 '16 at 17:27
  • @ypercubeᵀᴹ Corrected for any future viewers. Thank you again for pointing that out. – Nicholas May 3 '16 at 17:33
6

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.

  • I agree but I fear the compound key design might cause me issues with and ORM like EF or NHibernate. Your opinions? – Nicholas May 3 '16 at 16:03
  • You may try but I don't think that it might cause any issues. This is a very typical database design and NHibernate uses composite-id to use these type of compound keys (nhibernate.info/doc/nh/en/…) – DeadEye May 3 '16 at 16:16
  • 3
    if the ORM you use cannot handle composite PKs, you can make this (EmployeeID, AddressID) a UNIQUE constraint instead. if - even worse - the ORM requires a single-column PK, you can add an IDENTITY or a SEQUENCE column, too, and make it the PK. (many ORMS are that bad, really. Not sure about EF or Hibernate. But they shouldn't dictate table design.) – ypercubeᵀᴹ May 3 '16 at 16:19
  • 1
    Thank you, I wasn't really trying to imply my design would be dictated by my ORM and would gladly pick up a different ORM to match my needs but in every example I have seen online I dont really see talk of tables designed like my above example so I figured my design ideas had to go against some hard principle or something. Rarely do I see examples that use compound keys and even rarer do I see examples where you don't have a "table id" which I don't see a need for in the above scenario. Thank you all for the assistance. – Nicholas May 3 '16 at 17:22
  • 1
    I have compound keys all over one of my databases and Entity Framework handles it perfectly fine. – Logarr May 3 '16 at 20:04
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.

  • This should be the answer. If the "bridge" table has an address type on it, you can create the unique constraint there for EmployeeId + AddressTypeId. The address table should just contain the raw address data, and not what type of address it is. – Greg Burghardt May 5 '16 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.