Different relationship between 2 entities

Question

In my project I have a part where says -

An airline has many flights and each flight has one source (airport) and one or more destinations (airports). For each source destination pairs, you have to record the distance.

According to this statement I have done an erd like this -

                   _________
                  | Airline |
                   ---------
                       |
                      has
                   ____|____ 
                  | Flights |
                   ---------
               /               \
            s.has             d.has
      _______/______       ______\_______
     |source_airport|     | dest_airport |
      --------------       --------------

• Now I am having problem with the last attribute distance. How should I place it with the above erd?

• Alternately I am thinking the Airport will be a single entity where Flights & Airport will have a relationship between them. But if this happens how to distinguish the source and destination part between them as well as solve the problem with distance attribute?

Answer 1

Too long for a comment. You may represent a given flight using two tables, one for locations, and the other a junction table relating one location to another.

CREATE TABLE locations (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR NOT NULL,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL
);

CREATE TABLE flights (
    source_id INT NOT NULL,
    dest_id INT NOT NULL,
    PRIMARY KEY (source_id, dest_id),
    FOREIGN KEY (source_id) REFERENCES locations(id),
    FOREIGN KEY (dest_id) REFERENCES locations(id)
);

So, now a flight is simply an entry in the flights junction table. The metadata for each airport sits in the locations table.