Table relationships

[emenyu]

Hello every Body, I am in real need of help. I am developing Flight Management system database and I have basic training in Access.

My questions are.
1. How do I create a perfect relationship between 4 most important tables in my database that is Btn ,Contact List, Flights, Bookings and Budget codes. This are the Tables that I have created
(Contacts, Bookings,Flights, Aiports, Flightstatus,Companies budget code)

2. How can I link them in search away that I can access atraveller from contacts to Bookings by a click away without much typing the information in to a booking.

3. How do I create a flight form so that each flight represents a number of Bookings, by a click on each flight I can see the Number of p`ple booked.

Thank you
Benjamin E

 

[melissa_may1]

Hi Benjamin,

Sounds like you're starting on a fun project!

It also seems that your questions fall into two categories: (1) How best to set up the tables and their relationships and (2) How best to retrieve the data.

First, you're on the right path by asking questions about the tables and relationships. If they're set up properly now, the rest of your work will be much easier!

So, you'll need to map out the information that you need for each table, then figure out how each table is related to the others.

For instance, a Contact wants to Book a Flight to a particular Airport. So, four tables will be affected (Contact, Bookings, Flights, Airports) and you'll need a way to relate to them all.

At the very least, to process this request you'll need to know something like this:

Contact - Contact ID, Name, Address, Phone
Bookings - Booking ID, Contact ID, Flight ID
Flights - Flight ID, Origination Date, Origination Time, Origination Airport ID, Destination Airport ID
Airports - Airport ID, Airport Code, Name, City, State/Province, Country

So as you can see, you need to define all of the work that needs to be done (Add/Change/Delete a Flight, Add/Change/Delete a Contact, Add/Change/Delete a Booking, etc.) and define the data that is affected.

In addition, you need to define the "business logic" that controls each function. For instance, What do you do if a Flight needs to be cancelled? What happens if you try to delete a Contact that has active Bookings?

If this sounds like a lot of work, well it is! At least if it's done properly.

Your project could be relatively easy, or could become a huge application, depending on your reasons for creating it.

You might also find that Access is not the best way to develop it, depending on factors such as the number of simultaneous users and the size of the database.

You will also need to take special care on how certain functions are carried out. For instance, what happens if you query for a particular flight, find that there is room on the flight, fill out the booking form, but then find that someone else has booked the flight in the meantime, and there's now no more room?

This project can be a very interesting and rewarding one, but it isn't the easiest one to accomplish.

I hope this helps, and doesn't discourage you!