(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:
phone. Here they are:
Now, I have such entities as
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 (
third_party) dependent on
entityand 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
entityisn't connected to to multiple types of other entities (e.g.
client_rep.entity_idcould very likely be equal to
other_party.entity_idand 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, where
client_rep_email.email_idcould be equal to
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_idis not equal to
- I have to create a entity-specific rep (ie.
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).