Database Design - One table related to another table

[Scott]

My database includes (amongst others) two tables...one for flights completed
and one for airports. I've related the airport table to two fields in the
flights table...one-to-many to 'departure' and 'arrival'. Is that considered
good practice? It's worked so far, but I'm not sure if there were another
way to design this part of my database.

Thank you,
Scott

 

[tina]

there's no reason you can't (or shouldn't) relate one table to another table
more than once. typically, in the Relationships window, the first instance
of the second table shows the table name, and the second instance of the
second table tacks a "_1" on the end of the name.

hth

 

[John W. Vinson]

My database includes (amongst others) two tables...one for flights completed
and one for airports. I've related the airport table to two fields in the
flights table...one-to-many to 'departure' and 'arrival'. Is that considered
good practice? It's worked so far, but I'm not sure if there were another
way to design this part of my database.

Thank you,
Scott

That's perfectly legit. The departure and arrival locations of a flight are
(hopefully!!!) both airports and they are independent attributes of the
flight.

 

[Armen Stein]

That's perfectly legit. The departure and arrival locations of a flight are
(hopefully!!!) both airports and they are independent attributes of the
flight.

And a good naming convention will help you here. In our shop we would
name the two foreign keys in the Flight table something like:

AirportKey_Departure
AirportKey_Arrival

Regarding the Relationships window, Access requires two instances of
the Airport table. However, you can carefully position the second one
over the first one to save space on your diagram. Then the two
relationship lines will look as though they connect to the same table.

Another way is to position the second Airport table directly below the
first one, but with its height reduced to just showing the table name.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com