Best design for a many:many relationship issue

database-designnormalizationschema

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

Best Answer

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.

Short answer:

Either option 1 or option 2:

In-line (one address per source record)
Child table with an FK linked to source record (allows >1 address per source)

Longer...

A separate address table will normally have an FK to some parent record.
Each address will maybe have a type (billing, delivery, etc)

Addresses are not shared or reusable or finite: that is, each address for a source entry has little in common with other entries.

different source records wouldn't have shared addresses (eg many-many in the middle between source and address)
separate address table with FK(s) can't have multiple parents either
having the FKs on source records to some address table would be unusual.

Finally, consider how you use addresses. You add or update them. You don't look for an existing address and link to that. Each address is per source only.

Ms-access – Database Design Regarding a Relationship for Patient Visit

The first thing that catches my eye is what appear to be separate tables for each exam. This could get rather difficult to maintain in the future: if the structure of exams changes you need to update n tables. Also, any aggregate queries that cover all exams for 1 patient will need to join n tables.

I'd suggest a structure like this:

patient_exams
-------------
  patient_exam_id (PK)
  visit_id (FK)
  patient_id (FK)
  study_id (Fk)
  exam_seq_num 
  (other fields)

Use exam_seq_num to track which exam number the record is for a patient. You can use a composite key containing patient_id, visit_id, study_id, exam_seq_num to ensure that you don't get exams for a patient with duplicate sequence numbers. You'll still need a bit of code to create the correct sequence number, maybe an on-insert trigger.

UPDATE:

Ok, so now it's clear that the exame tables are actually for different types of exams. You could have something like

base_exam
---------
  id (PK)
  patient_id (FK)
  exam_date
  (other stuff)

Lumbar_exam_details
-------------------
  lumbar_exam_id (PK)
  base_exam_id (FK to base_exam.id)
  (other specific fields)

blood_exam_details
------------------
  blood_exam_id (pk)
  base_exam_id (FK to base_exam.id)
  (other specific fields)

All of your exam detail tables reference the base_exam table, which stores common fields for all exams (such as the date of the exam, the patient who was examined, etc...).

If you really want to have a "display name" for exam types, I would do that in a view that overlays the specific exam table. For example, the query for lumbar_exam_view might look like:

SELECT *, "Lumbar Exam" AS DISPLAY_NAME
FROM LUMBAR_EXAM_DETAILS

Use this view in any queries/reports on lumbar_exam_details you will have access to display_name anywhere that you want the user-friendly string.

If you need the display name to be stored as actual data, you can add an exam_type_id field to base_exam and then have it point to an exam_type table:

exam_type
---------
  id
  display_name

Data:

exam_types
ID  | display_name
------------------
1   | Lumbar Exam
2   | Blood test

Now your base exam records have an ID that points them to the correct user-friendly string. Note that this does not ensure that the exam detail record is of the correct type (i.e. it is possible, for example, to have a base_exam record that is referenced by lumbar_exam_details, but the base_exame record erroneously references the display name "Blood Test") - it only works on the display name.

Best Answer

Related Solutions

Physical Address Schema

Short answer:

Longer...

Ms-access – Database Design Regarding a Relationship for Patient Visit

Related Question