I have two types of users: a CRM user and a system user. We have a record of every CRM user, thanks to a nightly sync that we run (so we don't have to hit the CRM API every time). We have system users that don't have CRM accounts. We also have CRM users that aren't in our system. If a system user happens to have a CRM account, we want to link the two accounts together. So, this is an optional-to-optional relationship.
Originally, I put a foreign key on the CRM user table. This was enough for us to determine if a system user was in CRM or the other way around. I could have put the foreign key on the system user table just as easily. However, there's the off chance that, in the future, we will be linking other types of accounts to our system accounts, as well. I didn't want to keep adding new null-able columns to the system user table when I could just as easily add them to new tables.
I am using Entity Framework and it seems to hate optional-to-optional relationships. The only way I have been able to configure this relationship is using a one-to-many relationship. In the system user entity, I have a collection of CRM users. This works, except the code needs to constantly say user.CRMUsers.FirstOrDefault() which makes the code awkward and hard to read. Going in the opposite direction is fine, because the CRM user just links to a single system user (or null).
This has got me thinking that maybe I am representing optional-to-optional relationships incorrectly in my database. There's no clear reason why the foreign key should be on one table or the other. Perhaps a many-to-many table with a unique constraint is better (but I think that would mean having a collection in both entities, yuck!).
So, how should I be representing these types of relationships? Will EF understand that representation?
