0

(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:

enter image description here

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:

enter image description here

That's still not that bad, but when the third party comes into play, that's what I get:

enter image description here

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:

enter image description here

Much, much nicer. But:

  • Now I make each of the the entities (client_rep, other_party, third_party) dependent on entity 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 to other_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, where client_rep_email.email_id could be equal to other_party_email.email_id.

Another way to solve this problem would be to create entity-specific tables for phone and email.

enter image description here

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:

enter image description here

I could, of course, create a specific rep for each entity, like that:

enter image description here

This layout has other problems, like the others.

  • No way of being sure that client_rep.rep_id is not equal to other_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).

0

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

  • Isn't that what I did by introducing the entity table, which was referenced by phone_number and email, to which client_rep, other_party and third_party where related? I think I like that approach most, too. – moskalak Oct 31 '14 at 10:59
  • Kind of, if Entity is the supertype then it should relate to one or more of the subtypes. All common information across the subtypes can go into the supertype, for example name, the specifc_values should go in the appropriate subtype. There is no need to have the client_rep_entity other_party_entity etc tables because the relationship between the supertype and subtypes should be 1..1. You should however have associative tables between entity and phone and email. – Pixelated Oct 31 '14 at 11:11
  • Also if the supertype can only be related to one subtype then you can add a typeID column to the supertype table. This then means you can query the supertype table to quickly get related subtype information. – Pixelated Oct 31 '14 at 11:13
  • Right, the party_entity tables are completely unnecessary. Thank you. But one supertype can be related to one of multiple subtypes, so I guess all I need is move all of the common columns to the entity table and insert entity_id foreign key directly into each subtype. Thank you! – moskalak Oct 31 '14 at 11:37

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.