Best design for a many:many relationship issue 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).
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.