Table Design Help (1 Viewer)

xraive · Jun 1, 2009

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 itinerary 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.

Atomic Shrimp · Jun 2, 2009

It looks pretty sound to me.

Doesn't there need to be another link between ItineraryDetails.ToCityID and Cities.CityID?

The table name ItineraryDetails is misspelt, as is the field name Company in that table. (nitpick, I know, but now is the time to fix it)

xraive · Jun 2, 2009

You are right I have about the link between ItineraryDetails.ToCityID and Cities.City.

I have redesigned it to look like this what do you think?

xraive · Jun 2, 2009

Also what about linking the Cities.CityID with the Employees.City or Suppliers.City. Does this make it too cluttered

Atomic Shrimp · Jun 3, 2009

xraive said:
You are right I have about the link between ItineraryDetails.ToCityID and Cities.City.

I have redesigned it to look like this what do you think?

Hmmmm... I think it's actually looking a bit less normalized in this form - the tables detailing trips, hotels and taxis are all pretty similar in structure - that's often a case of your data suggesting that you combine them into a single, multi-purpose table - maybe 'ItineraryEvents' - where you might just have an event type field (Hotel stay, taxi ride, flight, etc) to define what type of data is on that row.

I don't think it would be particularly bad, in that case, to have fields that are only relevant/populated for some event types, and left blank for others - arrival date/time, for example, is more relevant for a flight than a taxi ride.

Doing it that way permits more flexibility when new kinds of itinerary event arrive on the scene - and they will - people will start travelling by bus instead of taxi, or might need to catch a ferry, or stay overnight in a sleeper car on a train, rather than a hotel... - if it's just a new event type for your table, it's easy to accommodate - if you have to add a new table for it, it's a nightmare.

xraive · Jun 3, 2009

Hi Mike,

Well that's the reason behind the Trips table. It relates to the TransportationType table which consists of different methods of travel for the trip(i.e. Train, Bus, Personal Car, Plane, Taxis...). Now the TripHotels table and TripTaxis table would then relate to every trip. Now what I can do is combine TripHotels and TripTaxis which might be need for every trip into one table. I can do TripEvents table which would be the likes of Hotel stay, Taxi booking.

What would you think of this design.

Atomic Shrimp · Jun 3, 2009

Is there only ever one travel authorisation per trip? (and one trip per travel authorisation?) - if so, those could be one table, with all of the events/details of the trip (taxis, hotels, tents, camel rides, etc) being described by rows in a trip details table.

xraive · Jun 3, 2009

Hi Mike,

There is one travel authorization per traveller but many trips per travel authorization. This is for the different legs of the trip.

Atomic Shrimp · Jun 3, 2009

But is there any particular need to keep the various legs of the trip distinct from each other?

What I mean is:

Is it a travel plan comprising one or more legs, each leg comprising one or more itinerary events?

Or is it just a travel plan comprising a series of itinerary events?

Is there a definite need to organise it into legs?

xraive · Jun 4, 2009

Well Mike originally I wanted for every travel authorization there are one or more trips and for each trip there would be trip details which be comprised of Hotel, Taxi, Car Rental... accomodations.

But I decided against it because for every leg of the trip you might have 1 or no entries in the trip details table.

So I tried to have a the current setup which is for evey travel authorization there might be one or more trips, one or more hotel accomodation, taxi..

I tried it this because I thought it would be better for reporting purposes. Now I think it's just getting complicated.

what do you suggest?

should i stick to the current formula which is one travel authorization has many events (trips, hotel,) or stick to one travel authorization has one or many trips and each trip would have many events.

Atomic Shrimp · Jun 4, 2009

I was going to say that having TAs>Trips>Events might make it simpler to add an event in the middle of an itinerary, but as long as the events have dates/times as appropriate, you could just have TAs>Events and sort the events by date.

Unless there is some definite need to identify a group of events as being a distinct trip/leg within an authorised travel schedule, then I would dispense with the trips table and just treat the events as child records of the travel authorisations.

It also does away with the ambiguity of whether travel from A to B is part of leg A or leg B

xraive · Jun 9, 2009

Thanks Mike,

I decided to stick with the Travel>Trips>Events>EventDetails Method

Table Design Help (1 Viewer)

xraive

Registered User.

Attachments

Atomic Shrimp

Humanoid lifeform

xraive

Registered User.

Attachments

xraive

Registered User.

Atomic Shrimp

Humanoid lifeform

xraive

Registered User.

Atomic Shrimp

Humanoid lifeform

xraive

Registered User.

Atomic Shrimp

Humanoid lifeform

xraive

Registered User.

Atomic Shrimp

Humanoid lifeform

xraive

Registered User.

Similar threads

Users who are viewing this thread