How to get this working...

[mdixson]

Right below is my ERD and i need to get it so a seat is reserved for a customer, anyone got any ideas how?

here is the image link: http://img.photobucket.com/albums/v205/mdixson/ERD.jpg

 

[Mirage]

At a quick glance...

What's the relationship between Customers and Passengers? (If there even is one that is)

Give the Seats table a date/time field, so that you can book out seats for a certain amount at a time. You'll be able to only allow seats which are free to be selected on your form.

 

[mdixson]

problem is that the seatID is an auto-number and i need to be able to search seats on flights; and thats were i'm stumped

 

[Mirage]

If the SeatID is the same as saying "This number identifies a certain seat on a certain flight" then it won't work as you currently have it.

In between the Passengers and Seat tables have a table such as:

tblPassengersSeat
RelPassID, Number
RelSeatID, Number
RelFlightID, Number

Everytime you add a Passenger to a seat get it to insert the PassengerID, SeatID and FlightID into this table.

You will be able then to limit the list of available seats to only those that do not appear in tblPassengersSeat where FlightID = RelFlightID and SeatID = RelSeatID.


Please excuse if this doesn't make too much sense as I'm in work and photbucket is blocked, I'm working from what I remember earlier.

 

[Mirage]

Infact, the table wouldn't need Passenger ID in there, all you would need an additional table to do is link SeatIDs to FlightIDs, so that you can see which are booked out.

 

[pinkaardvark]

You seem to be repeating address and name data across customer and passenger tables, while these may not always be the same entity it would be better to normalize out that data into an addresses table. Also you shouldn't have a fix crew list in my opinion as different aircraft will require different numbers of crew, so I'd create a crew manifest table that related to crew.