(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).