Hello to all,
I would like to set up a MySql database that will
store trip information for a milage lookup
program. The user will enter the origin city/st and
the destination city/st for some predefined trips.
Then the query will need to return the milage broken
down by state. For example, for an origin city in NJ
and a
destination city in OH, the program would need to
return how many miles were traveled within NJ, PA, and
OH as well as the total miles.
It would be very inefficient to use one large table
with columns for each state in the USA because most of
them would be empty for a given trip.
Following the rules for normalization, a two table
approach is much better. It could use a primary table
to store the origin, destination, total miles, and a
unique reference key. The secondary table could hold
the reference key as a foreign key and a record for
each state name and mileage. This is shown as:
trip(tripID,originCity,originSt,
destCity,destSt,totalMiles)
itinerary(tripID,stateName,miles)
Perhaps an enumerated column could be used in the
secondary table, such as:
itinerary(tripID,enum{NY,NJ,PA,OH,KY,TN,...}, miles)
If anyone has had a similar design challenge I would
appreciate hearing any comments you might have because
I am not sure which way is better.
Thanks -- Michael
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
