A study in JOINs with many-to-many relationships

Question

Consider, if you will, the following tables:

This may seem like a strange structure, but allow me to explain. The goal of this structure is to create a report listing all people, excluding those on vacation (for simplicity, we'll pretend that the vacation record will only exist if a person is on vacation.)

The reason I have the Vacation table at all is because the Event table is a more general use table for "insignificant" events, whereas the Vacation table includes more details such as the location and date.

What I can't seem to figure out is how to design a query that includes, say, Person.personId and Vacation.location, but only those personIds that exist in Person_Vacation. Or the inverse, only personIds that DO NOT exist in Person_Vacation.

Also, does this seem to be the best way to implement this solution, design-wise? Any ideas on what I might have done wrong or suggested improvements?

EDIT: Maybe i'm just not too good at communicating my intentions: turns out getting only the persons on vacation is easy, but i would like to perform the inverse, basically all persons NOT on vacation.

Answer 1

Design-wise I don't see why you need Person_Vacation. If I understand your design correctly, every vacation is an event (i.e. "inheritance"), so the connection between Vacation and Person can be made through Person_Event. Then the queries would be:

Persons in vacation:

SELECT P.PersonID, V.locaton
FROM Person P, Vacation V, Person_Event PE
WHERE P.personid = PE.personID AND PE.eventid = V.eventid

Persons in NON-vacation events:

SELECT P.PersonID, E.title
FROM Person P, Person_Event PE, Event E
WHERE P.personid = PE.personID AND PE.eventid = E.eventid
AND E.eventID NOT IN (SELECT eventid FROM Vacation)

Persons not on any vacation:

SELECT P.PersonID, E.title
FROM Person P, Person_Event PE, Event E
WHERE P.personid = PE.personID AND PE.eventid = E.eventid
AND P.personID NOT IN 
  (SELECT personid FROM Person_Event PE, Vacation V WHERE PE.eventid = V.eventid)

If you want to go with your own design, then you have to decide if you will insert a person's vacation only in Person_Vacation or in Person_Vacation and Person_Event. If you do the former, then the queries above would become a bit simpler.

However I would still vote for my design, because what you are essentially doing is prematurely splitting data just to get a bit of performance / simplicity. This may not pay off in the long run.

Answer 2

Probably I didn't full understand in your database design. If Vocation is also an event and the Person_Event table has all events which conserned the person personId then the table should contain the subset of Person_Vacation and one can eliminate it. Moreover all persons have sometime vocation so I would find more logical to include a dates in some tables.

Nevetheless how I understend your database design you can receive the results which you want with a query like following

SELECT p.name, e.title
FROM Person AS p
    INNER JOIN Person_Event AS pe ON p.personId = pe.personId
    INNER JOIN [Event] AS e ON pe.eventId = e.eventId
    LEFT OUTER JOIN Vacation AS v ON e.eventId = v.eventId
WHERE v.eventId IS NULL