Search code examples
ms-accessone-to-one

Acces, multiple one to one connections question

I have a problem with acces, im trying to make database of airport and cant realy make one to one - one to one - one to one connection beetwen ticket, passanger and luggage. The idea is that one passanger can have one ticket and one luggage, and when im doing it like it is on picture i cant acces luggage from ticket neither from passanger only from luggage itself.

My diagram

Solution

  • Mainly the diagram should be something like:

    enter image description here

    Simple Data and Keys:

    enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

    Sample query(all tickets with relevant linked data):

    SELECT Tickets.Ticket, 
       Tickets.Person, 
       Flights.Flight, 
       Luggages.Luggage
FROM Flights 
INNER JOIN (
   (Persons INNER JOIN Tickets ON Persons.ID = Tickets.Person)
LEFT JOIN Luggages ON Tickets.ID = Luggages.ID)
    ON Flights.ID = Tickets.Flight;

    enter image description here

    Second view was the purpose, so it up to your side to do the adjustments.

    enter image description here

    Note: Person 3 do not have luggage. Person 1&2 use same flight.

    MS Access was used, but design is not bound to a specific rdbms (only dialects adjustments for a particular implementation)

    Also, there is an error on relation Persons-Tickets which should be 1-n.(similar Flight-Tickets).

    Still additional constraints could be bound also. Eg: a person can be just on one flight is a specific time.