Hi All, I am trying to design a travel requests app in MS Access. The program should be able to take flight/car, hotel, or taxi, arrangements and also get confirmation. Every trip sometimes requires a hotel, taxi. I have attached an image of the relationhips that I have designed. Can you take a look and see if I am going in the right direction or if there are suggestions that I can make.
-
-
Attached Database
Here I have attached the database.
This database must be able to confirm transportation (Flights, Personal Car) and hotel reservations if required (Hotel, personal accomodation)
Thank you,
Rezene A.Comment
-
It doesn't look too bad.
Just some pointers:
1. 'ItineraryAccom odation' is related to the 'itinerarydetai ls' and the 'suppliers'. It should therefore go between those 2 tables.
2. Since you are using a 'city' table to indicate the FromCity why not also use it for the ToCity. Therefore 2 links to 'City' table for your 2 city fields in the 'itinerarydetai ls' table.
3. What do you do if 2 or more employees are taking the same trip and therefore have the same itinerary details? The way it is set up currently you would have to enter all the same info for each employee that is on that trip.
4. If you wish to print the itinerary out in order based on the order that each itinerarydetail occurs you will have trouble in the following scenario:
a - Assume there are a number of steps in an itinerary.
b - You are entering data into your itinerary table.
c -After you get the data in, you realise you need to add an itinerary stop for a date that already exists (ie: checks into 1 hotel and checks out that evening and checks into a new hotel the same day.)
d - When you try to print the itineray, what will you base the sort order on? Cannot be Checkin date since you cannot indicate which iternerary detail comes 1st when you have more than 1 checkin on the same day. You could base it on checkin/checkout I guess but that gets complicated - also what if checkin/checkout are same day. Cannot base it on the autonumber and itineraryID - same problem.
Hope that rambling made sense.
5. Use naming conventions throughout your tables for all field names. ie: The tables 'TravelCategori es' and 'TravelTypes' have fields called 'notes'. As you get programming you may get mixed up easily what fields you are referring to. You may want to name fields using the prefix as the same name as the table followed by the field description. ie: travelCategorie sNotes and travelTypesNote s.
6. Don't put programming in your tables. ie: For your 'Suppliers' table your 'suppliertypeID ' is based on a combo box. I would also do all that cosmetic stuff in the forms. Including all your caption headings etc.
Anyways that's it for now. Hope this helps.
cheers,Comment
-
Table Design
Can you suggest an alternative way for me to set this up? I am seriously hitting a roadblock.Comment
Comment