1

I'm having difficulty designing an appropriate data model for my application.
In my application I have pairs of participants who will complete several activities together. Each pairing will have 2-3 conversations in the course of its existence. Each conversation may have many messages that are authored by either of the participants in the pairing.
The best model I have is this:

         PAIRINGS
          /     \
         /       \
        V         V  
PARTICIPANTS     CONVERSATIONS
-pairing_id      -pairing_id
       \          /
        \        /
         V      V
         MESSAGES
         -participant_id
         -conversation_id

However this model allows for the author of a message to be outside of the pairing that is associated with the conversation and that seems wrong. Anybody have better suggestions for how I could structure my data model?

Selah
  • 7,728
  • 9
  • 48
  • 60
  • This is just too vague. No standard notation. Not Enough detail. – Jonno Jun 18 '12 at 19:45
  • I don't have enough reputation to post a picture so I'm unable to include a diagram. If there are any questions I can answer I'd gladly do that. – Selah Jun 18 '12 at 20:14

1 Answers1

2

This is a common problem. Add the pairing_id in the MESSAGES table and change appropriately the two FOREIGN keys to include this column:

          PAIRINGS
          /     \
         /       \
        V         V  
PARTICIPANTS     CONVERSATIONS
-pairing_id      -pairing_id
       \          /
        \        /
         V      V
         MESSAGES
         -participant_id
         -conversation_id
         -pairing_id

Most DBMS will also require that you add UNIQUE indices, on PARTICIPANTS (pairing_id, participant_id) and on CONVERSATIONS (pairing_id, conversation_id)

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • interesting... is this a common pattern that i could read more about? is there some term i could search for on the internet? – Selah Jun 18 '12 at 20:43
  • Yeah, I should say it's common in diamond-shaped design. See this similar question: [SQL: Normalization of database while retaining constraints](http://stackoverflow.com/questions/7183039/sql-normalization-of-database-while-retaining-constraints/7183985#7183985) or this (more complex) one: [Choosing from multiple candidate keys](http://stackoverflow.com/questions/9082575/choosing-from-multiple-candidate-keys/9082972#9082972) – ypercubeᵀᴹ Jun 18 '12 at 21:32
  • Beware that this requires a compound (composite) PK or Unique index and a compound Foreign Key - and that some ORMs have difficulties with compound FKs or cannot work at all with these. – ypercubeᵀᴹ Jun 18 '12 at 21:38