Microsoft Access Edit Relationships Primary Key as Foreign Key Twice in Related Table

Question

I'm working on an assignment for an ERD and Access database where I need to develop an ERD for a company that has a fleet and must be able to schedule flights. I've prepared the relationship diagram to have two relationships one to many for flights from airports and flights to airports (image 1)

ERD relationship diagram

However, I'm a bit confused as how to "translate" this diagram in Microsoft Access. I want to be able to connect both entities (the airports entity and the airplaneflightschedules entity) together but I'm thinking I did so incorrectly. In the Access relationship diagram (image 2) I have them connected but I'm pretty sure it's incorrect and I'm not even quite sure if I understand what it means to have them connected like this in Microsoft Access.

Access relationship diagram

Can someone tell me what a list like this does in Microsoft Access? How does it impact my overall database? And is this relationship ideal for a case like this?

Answer 1

the relationship diagram in Access provides permanent joins between tables

generically: a list table is essentially static i.e.. list of States, of Airports, etc.

a transaction table is actively adding/changing records....

we don't want people free form typing in their state or their airport or whatever cause they make typos - - we want them to select from the list table....

in the Access join diagram you dupe the list table so there is States_1 States_2 and join each - - you don't make a bunch of joins to the same list table when it is reused.

many developers prefer to not join at the table level for lists - avoiding the 'look up' field type; and instead implement look ups at the form/control level - as the selector control (combo box) has a row source property where you can designate the list table.... this approach is more flexible

Answer 2

Well, in effect, this setup is "more" of a lookup then it is a enforced relation ship.

I mean, you can add new airports, even delete airports - and the Fight schedule table really not involved.

However, by enforcing a relationship, then you ONLY get the added bonus that you would not be able to delete (by accident or intention) a airport record (that is currently being used. If not used, then you will be able to delete.

But you are as noted free to delete and add flights (and the airport table don't care). And as noted, you are free to add (or delete) Airports, and the flight table don't care either (except if the airport is currently being used).

But, to enforce the relationships? In the relationship designer, you drop in your table (Flight schedule), and then drop in your Airports table. Setup the first relationship (and it should be a left join).

Now for the 2nd relationship (DestAirPort), then simply right click and add the Airports table a 2nd time.

You wind up with this:

And as noted the database will and can function quite well without this relationship, but the above is still valuable, since it does prevent deleting of a airport in use, and the query builder will use the join defaults. I did set above as a left join, but to be fair, I should perhaps have used a inner join, and you thus get this: (note the lack of arrow heads)