Table Concerns.... (1 Viewer)

[Kyp]

Trying to build a flight time tracking database.
I’ve created the tables with all the info that is needed for the app, and set relationships the way I think they need to be.

tbl_Flights : tbl_Leg (One to Many)
A flight consist of 1 day.
Each flight can have many legs.

I’ve created a form based on a separate table, tbl_Flight_Log.
My idea was that using queries to pull values from other tables/queries into the form and save to the flight log table.

Is this the correct way of accomplishing this task?
My thinking is that I am duplicating data, but it does work. If this is not correct, please enlighten my on the proper way to accomplish this.

Cheers!

 

[The_Doc_Man]

When you see fields in a table for which you have suffixes _1, _2, ... , _6, you have a non-normalized table. The items represented that way should be in child tables of their own. You only show one relationship in your relationship diagram, and while that is absolutely possible, it is unlikely to be correct. But there is another issue... if you can pull all the data you need (via query) to build one entry in the flight log table, you don't need the flight log table at all - for computational purposes. (I understand you might need it for regulatory reasons.)

 

[Kyp]

When you see fields in a table for which you have suffixes _1, _2, ... , _6, you have a non-normalized table. The items represented that way should be in child tables of their own. You only show one relationship in your relationship diagram, and while that is absolutely possible, it is unlikely to be correct. But there is another issue... if you can pull all the data you need (via query) to build one entry in the flight log table, you don't need the flight log table at all - for computational purposes. (I understand you might need it for regulatory reasons.)

@The_Doc_Man I appreciate your time to review this.

The fields Pax_1-Pax_6 are place holders for passengers. There are 6 seats available on each flight.
While this may not be relevant to your statement concerning non-normalized table, I'm not sure how I would keep record of each passenger, and their assigned seat.

Concerning the flight log table, I did not think it was correct to have a separate table with duplicated data. (I read that somewhere here on the forum)

My thinking was that if I am pulling the required data from other tables/queries, I needed a place to store them. (for reporting purposes)
That is why I created another table. While this database is quite simple, I am having trouble understanding, a mental block or something...

Please elaborate on how I should normalize the passengers (Pax) in the table.

Thanks!

 

[The_Doc_Man]

You add a PassengerLeg table which MIGHT look like

PFSID, prime key, probably autonumber - passenger/flight/seat info
Seat number
Flight number
Passenger info - which might then link to your individual passenger table - but this leads to a question regarding frequent fliers and what do you mean by "Company" in the PAX table and Leg table? Somehow that smells of yet another previously unidentified table since I don't see table that lists companies yet I see references to Company more than once.

Then you take the PAX info out of the Leg table. You establish a one/many relationship between Leg and PassengerLeg, which allows you to list the passengers by seat number and if you have an empty seat, you just don't have an entry there.

There are more complexities here, such as what happens if your Passenger James Fliesalot changes companies? You have him in the passenger table with a company ID. Does he become another different passenger if he changes companies? If you need to track the company, I understand it is needed... but the question is WHERE would you keep that info? If it is date-dependent, then it should not be with the passenger table.

Note also that the PFSID field as a PK might be overkill or might not, depending on the resolution of the "Company" issue. A table that has no dependent tables under it in the table structure hierarchy does not have to have a prime key (because nothing would link back to it.) If you need a link to it, whatever it is needs a PK. If you don't need a link,... not needed so much.

 

[Kyp]

You add a PassengerLeg table which MIGHT look like

PFSID, prime key, probably autonumber - passenger/flight/seat info
Seat number
Flight number
Passenger info - which might then link to your individual passenger table - but this leads to a question regarding frequent fliers and what do you mean by "Company" in the PAX table and Leg table? Somehow that smells of yet another previously unidentified table since I don't see table that lists companies yet I see references to Company more than once.

Then you take the PAX info out of the Leg table. You establish a one/many relationship between Leg and PassengerLeg, which allows you to list the passengers by seat number and if you have an empty seat, you just don't have an entry there.

There are more complexities here, such as what happens if your Passenger James Fliesalot changes companies? You have him in the passenger table with a company ID. Does he become another different passenger if he changes companies? If you need to track the company, I understand it is needed... but the question is WHERE would you keep that info? If it is date-dependent, then it should not be with the passenger table.

Note also that the PFSID field as a PK might be overkill or might not, depending on the resolution of the "Company" issue. A table that has no dependent tables under it in the table structure hierarchy does not have to have a prime key (because nothing would link back to it.) If you need a link to it, whatever it is needs a PK. If you don't need a link,... not needed so much.

Let me chew on this for a bit. I follow, just not 100%