By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,045 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,045 IT Pros & Developers. It's quick & easy.

Access Database 3NF LinkTable Question

P: 20
I am new to database design. I am creating an airline flight scheduling database in Microsoft Access 2002.

I am having trouble uniquely identifying an airline name, flight number in out, type of aircraft, arrival and departure times,routefrom,routeto. All of this information is subject to change based on various factors.

I have attempted to bring the database to 3NF. I have tables for the following:

tblacCode
tblacFlightNumberIN
tblacFlightNumberOUT
tblacTypeOfAircraft
tblacArrival
tblacDeparture
tblacRouteFrom
tblRouteTo.

I have a 1 to many relationship for the following
tblacCode 1: N LinktableFlightNumberINOUT

First---I would like to know if it is possible to use a linktable called treltblUniqueIdentifier with a combination of all of the tables listed above primary keys to create a one to many relationship for tblacarrival, tblacdeparture ,tblacrouteFrom,tblacrouteTo and tblDaysOFOperation?

Second--I set all the primary keys to autonumber but I am having trouble with the foreign keys autoupdating when I enter test data directly into access tables.
Should I have use text codes instead. These codes have a chance of repeating based on exceptions but rarely do and they would be more meaningful in my linktableUniqueIdentifier.



Please help.
Mar 16 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I am new to database design. I am creating an airline flight scheduling database in Microsoft Access 2002.

I am having trouble uniquely identifying an airline name, flight number in out, type of aircraft, arrival and departure times,routefrom,routeto. All of this information is subject to change based on various factors.

I have attempted to bring the database to 3NF. I have tables for the following:

tblacCode
tblacFlightNumberIN
tblacFlightNumberOUT
tblacTypeOfAircraft
tblacArrival
tblacDeparture
tblacRouteFrom
tblRouteTo.

I have a 1 to many relationship for the following
tblacCode 1: N LinktableFlightNumberINOUT

First---I would like to know if it is possible to use a linktable called treltblUniqueIdentifier with a combination of all of the tables listed above primary keys to create a one to many relationship for tblacarrival, tblacdeparture ,tblacrouteFrom,tblacrouteTo and tblDaysOFOperation?

Second--I set all the primary keys to autonumber but I am having trouble with the foreign keys autoupdating when I enter test data directly into access tables.
Should I have use text codes instead. These codes have a chance of repeating based on exceptions but rarely do and they would be more meaningful in my linktableUniqueIdentifier.



Please help.
OK I think you have set up the structure wrong. You have broken it out too much.

tblFlight (Sample data in quotaton marks)
FlightID (Primary Key - Autonumber)
FlightNo "BA125"
IN/OUT "IN"
AircraftTypeID (Foreign key referencing the tblAircraftType table)
ArrivalDate "12/07/2007"
ArrivalTime "08:30"
DepartureDate "Null"
DepartureTime "Null"
RouteFrom "New York"
RouteTo "Null"

tblAircraftType
AircraftTypeID
Type "Boeing 747"

The FlightID is just the databases way to uniquely identify each record. Days of operation is a calculated value and shouldn't be stored but calcuated dynamically in a query. If the above structure doesn't fill your needs let me know what is missing.

Mary
Mar 16 '07 #2

P: 20
Thank You so much. I simplified the tables and it works!!!
Mar 19 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank You so much. I simplified the tables and it works!!!
Glad you got it working.

Mary
Mar 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.