Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.