Clarifications around crew members:
- Crew assignments are not normally the same from day to day. (If Charlie/Julie were the pilot/co-pilot on this morning’s United 678, they are very likely not going to be the pilot/co-pilot on tomorrow’s United 678.)
- All crew members who are certified to fly a type of plane can be either the pilot, or the co-pilot, for a flight that uses that type of plane. (There’s no such thing as “being certified to co-pilot a Boeing 747, but not certified to pilot it.”)
- Every crew member is a pilot on some flights, and a co-pilot on others. (They’re rotated so they don’t get too fatigued.)
- Your database does need to know who the pilot is, and who the co-pilot is, for a given day’s flight, not merely “who the two crew members are.” If I go to your database and ask “who was the co-pilot for yesterday’s American 113?” your database is not allowed to respond, “well, Sallie and Jeff were the two crew members, but I’m really not sure which one was the pilot and which was the co-pilot.” (Hint: I think you’ll want two different one-to-many associations in this part of your design, not a single many-to-many association.)
