You need middle tables between each one of your base tables
Tourist - touristflight - flight
Tourist - touristhotel - hotel
Each on of this middle table will hold the key from each of the base tables linking them together. This is what you do your reports on later.
So the flight time, flight number will store the data in flight table(same for other base tables), the middle tables only contain the key linking all base table, right? so how could i generate tour guide report?
middle table hold the information that is a unique combination for Tourist and Flight, such as Booking Number, Seat or anything equal to that.
Thanks again, how could i summarize the middle tables and generate the tour guide report by time.
Sorry, i still don't understand the trick. how could the user choose what things go first
So for the middle tables, the fields will duplicate? As there are 7 tables in total, right?
Do i need to link up the middle table?
Check in date
Check out date
so you mean i create the middle tables linking student to other tables(hotels, flight, school, tourist spot)(i don't need to link the hotels, flight, school, tourist spot together)
and then in the middle table contains the things related to the student. For example, hotel, date to this hotel, check-in time, check out time. Finally, i create a layout and put the middle tables fields in that layout and sort the record by date and student. Is that right?
Tourist table relates to TouristHotel and that table relates to Hotel
This is the best practice to handle information in any kind of relationship database
Thank you Hedman. So what records i should show in the report? since the middle tables stores record separately?
For each Tourist that you have Hotel information on you need one record in TouristHotel
For each Tourist that you have Flight information on you need one record in TouristFlight. One for the flight arriving and one for the flight leaving.
In your reports you then create a layout based on your middle tables and have a Sub Summary part based on Tourist on Sort your records on Tourist iD
The report layout is based on which middle tables as there are 4 of them or i just create 1 middle table that contain all information links to the tourist?
If you want to use one middle table for all four you need to add a field Type that is hotel, flight, school or tourist. Then on each of your base layouts you just set a filter in your portal to show middle table based on that Type.
if i use several middle tables, in my report layout, i could choose any one of them?