Best design for a many:many relationship issue

Question

We have three types of entity, each with their own table:

table:A(a_id), table:B(b_id), table:C(c_id)

Each of these entities can have any number of email addresses and linked Twitter accounts.

table:EmailAddresses, table:TwitterAccounts

What is the best way to relate these tables where the foreign key could be that of table A, B or C?

I considered this:

table:EmailAddresses(belongs_to_type,belongs_to_id), table:TwitterAccounts(belongs_to_type,belongs_to_id)

Where belongs_to_type would be A, B or C and belongs_to_id would be the respective foreign key.

So if A(1) has an email address, that entry would in the EmailAddresses table would look like EmailAddresses(A,1).

Answer 1

The solution you propose will work, though it requires you to get the value of belongs_to_type for a record to determine which table to join to. This could get messy.

I've never tried this, but I think something like this might work:

a
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

b
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

c
-----
  id (PK)
  contect_Ref_id (FK to contact_refs.id)

contact_refs
------------
  id (PK)

contact_ref_emails
------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  email_id (FK to emailAddresses.id)

contact_ref_twitterAccts
------------------------
  id (PK)
  contact_Ref_id (FK to contact_refs.id)
  twitterAcct_id (FK to twitterAccounts.id)

emailAddresses
--------------
  id (PK)

twitterAccounts
---------------
  id (PK)

So now each entity has a reference to a contact_Reference. The contact reference is used in the many-to-many tables so that each entity references a single contact reference, and the contact references can refer to many email accounts or twitter accounts.

Answer 2

If I understand your question correctly your three types of entities could be something along this line:

• Employee
• Business_Partner
• Customer

And each of them can have multiple twitter accounts and email address. If this is the case and you want a many to many relationship between each of them and each of the "address" tables your best bet is to create multiple "link" tables.

EmployeeXTwitter
------------------
Employee_Id (pk) (fk back to Employee)
Twitter_Id (pk) (fk back to Twitter)

EmployeeXEmail
------------------
Employee_Id (pk) (fk back to Employee)
Email_Id (pk) (fk back to Email)

etc for each of your entity tables. This means 6 extra tables and all of the overhead associated but your queries will be able to actually take advantage of the foreign keys and can go quite a bit faster. In fact if you need to you can create some views to go over top of the tables to simplify your work later.

Now I will say that while I think this is a simpler format to work with than @FrustratedWithFormsDesigner design his does have a major benefit in that if you move your contact information into the contact_refs table and out of the entity tables (A, B and C) then you have a more normalized design which will provide it's own benefits.