2

The Data

Let's say I have a grid representing all the people in a room and the conversations they have when they speak to each other.

John Susan Brian Rachel
John Reassurance Work Sports Movies
Susan Work Mumbling Travel Music
Brian Sports Travel Nothing Food
Rachel Movies Music Food Self Deprecating

The Database

Initially I considered just making a regular tables of People and Conversations

The Conversations table would be a key and topic participant1 participant2

The Dilemma

The order of the participants is totally irrelevant. A simple question "What does Rachel and Brian talk about," becomes a complicated query to run.

So one of two things must be done:

  1. Redundant data is added to the DB: (Rachel and Brian) or (Brian and Rachel)
  2. A ridiculously complicated query is needed to query the non-redundant data because you don't know if Rachel is participant1 or participant2.

Going with 1 above creates an unsustainable O(n^2) database model, while 2 seems like a DBA nightmare trying to query meaningful data.

The Question

How do I model a data structure like this? Is SQL even the right tool for the job or should I be researching something else entirely?

2 Answers 2

1

A "many-to-many" relationship is typically modelled using a "junction" (or "link") table with foreign keys pointing to the respective entities.

In your case you will have a Person table, a Conversation table, and a PersonConversations table with two attributes: ConversationId and PersonId.

Having said that, the task can be alternatively, and perhaps more naturally, modelled by a graph, and implemented using a graph database.

2

The Data

Storing the same combination of participants twice is a serious normalization error and should not be considered as an option. The data that is unique is in one of the corresponding triangular matrices, e.g.:

Brian John Rachel Susan
Brian Nothing Sports Food Travel
John - Reassurance Movies Work
Rachel - - Self Deprecating Music
Susan - - - Mumbling

The Database

The database should enforce the necessary constraints.

  • The key of Conversation is the composite (Participant1, Participant2)
  • For ensuring that a combination of participants is unique, we can enforce an ordering between Participant1 and Participant2, e.g. Participant1 <= Participant2.

enter image description here

CREATE TABLE Person
(
    Person VARCHAR(30),
    CONSTRAINT UC_Person_PK PRIMARY KEY CLUSTERED (Person)
);

CREATE TABLE Conversation
(
    Participant1 VARCHAR(30),
    Participant2 VARCHAR(30),
    Topic        VARCHAR(30),
    CONSTRAINT UC_Conversation_PK PRIMARY KEY CLUSTERED (Participant1, Participant2),
    CONSTRAINT U__Conversation_AK UNIQUE (Participant2, Participant1),
    CONSTRAINT Person_IsParticipant1In_Conversation_fk FOREIGN KEY (Participant1) REFERENCES Person (Person),
    CONSTRAINT Person_IsParticipant2In_Conversation_fk FOREIGN KEY (Participant2) REFERENCES Person (Person),
    CONSTRAINT Participant1_LE_Participant2_ck CHECK (Participant1 <= Participant2)
);

INSERT INTO Person (Person)
VALUES ('Brian'),
       ('John'),
       ('Rachel'),
       ('Susan');

INSERT INTO Conversation (Participant1, Participant2, Topic)
VALUES ('Brian', 'Brian', 'Nothing'),
       ('Brian', 'John', 'Sports'),
       ('Brian', 'Rachel', 'Food'),
       ('Brian', 'Susan', 'Travel'),
       ('John', 'John', 'Reassurance'),
       ('John', 'Rachel', 'Movies'),
       ('John', 'Susan', 'Work'),
       ('Rachel', 'Rachel', 'Self Deprecating'),
       ('Rachel', 'Susan', 'Music'),
       ('Susan', 'Susan', 'Mumbling');

The Query

A simple question "What does Rachel and Brian talk about," becomes a complicated query to run

A ridiculously complicated query is needed to query the non-redundant data because you don't know if Rachel is participant1 or participant2.

Assuming that you cannot just construct the WHERE clause with an ordered pair for Participant1 and Participant2, querying the above table is something like:

DECLARE @ParticipantA VARCHAR(30) = 'Rachel';
DECLARE @ParticipantB VARCHAR(30) = 'Brian';
SELECT *
FROM Conversation
WHERE (Participant1 = @ParticipantA AND Participant2 = @ParticipantB)
   OR (Participant1 = @ParticipantB AND Participant2 = @ParticipantA);

I don't see how it can get so complicated.

The View

You always have the possibility to get an unnormalized view of the data that contains all permutations.

CREATE VIEW Conversation_V AS
SELECT Participant1, Participant2, Topic
FROM Conversation
UNION
SELECT Participant2 AS Participant1, Participant1 AS Participant2, Topic
FROM Conversation;

DECLARE @ParticipantA VARCHAR(30) = 'Rachel';
DECLARE @ParticipantB VARCHAR(30) = 'Brian';
SELECT *
FROM Conversation_V
WHERE (Participant1 = @ParticipantA AND Participant2 = @ParticipantB);
1
  • In addition you could use instead of triggers on the view (and do inserts there) that ensures that (Participant1 <= Participant2) holds. LEAST and GREATEST could be used in the trigger. Jan 30 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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