(PLEASE NOTE: I am aware that in a few cases there are one-to-one relationships where one-to-many should be present. It's not intentional, it's just a mistake on my side. Take no notice.)
I am designing a database for my project and I've ran into a small problem.
In the database there will be a few entities that can be in a one-to-many relationship with tables: email
and phone
. Here they are:
Now, I have such entities as client_rep
, other_party
and third_party
that I want to relate one-to-many to the aforementioned tables. Since there are multiple of those, I need to create an intermediate table, in the following fashion:
That's still not that bad, but when the third party comes into play, that's what I get:
Getting nastier and nastier, right? But when I'd add more tables and more relationships, this stuff's getting, for my taste, too hideous and hard to manage.
Now, one solution I could come up with, was to add an intermediary table, like an "entity". Then, it could look something like that:
Much, much nicer. But:
- Now I make each of the the entities (
client_rep
,other_party
,third_party
) dependent onentity
and I'm not sure that's good. - I create a seemingly useless table containing only and ID, nothing else.
- This way I can't be sure that one value of
entity
isn't connected to to multiple types of other entities (e.g.client_rep.entity_id
could very likely be equal toother_party.entity_id
and I have no way of knowing that, except for by issuing a query, ie. from inside of my application. Still, I guess I had the same problem in the first design, whereclient_rep_email.email_id
could be equal toother_party_email.email_id
.
Another way to solve this problem would be to create entity-specific tables for phone and email.
That looks ugly at least, but this way I can be sure, that no phone number and email will be referenced by two different entities. Still, there are a few very specific and important problems with this layout:
- When I want to change the length of accepted phone numbers or email addresses, I will have to edit 3 times more tables. And imagine what if added one more entity?
- It's simply not right, because the same object (e.g. email address) is represented multiple times.
There is another way of doing that. Let's take a look at the client_rep
table. It's obvious, that in reality it looks like this:
I could, of course, create a specific rep for each entity, like that:
This layout has other problems, like the others.
- No way of being sure that
client_rep.rep_id
is not equal toother_party_rep.rep_id
. - I have to create a entity-specific rep (ie.
client_rep
,other_party_rep
,third_party_rep
.
But there's a slight, which is that I can enter entity's rep-specific values. Still, in case of some entities (e.g. other_party
), there won't ever be a rep per se. There will be only general contact information.
And I'm lost. There are to many solutions available and I can't choose the best one (although I can identify a few better than others).
Best Answer
I faced this same problem 4 or so years ago here https://stackoverflow.com/questions/3636061/database-design-similar-contact-information-for-multiple-entities.
In the end I went with my method one which did involve many more joins etc during the life of the project but I always liked the fact that contact information only had to be stored in one place, once in the database.
Another way to look at this is to maybe see if you can reduce the number of other tables. Client_rep, other_party, third_party all seem like types of users. have you considered a super type - sub type relationship. For example dbo.user is the SuperType with client_rep, other_party and third_party related as the subtype. You would then only need two associative tables dbo.phoneUser and dbo.emailuser between dbo.phone, dbo.email and dbo.user