database's table design with similar foreign keys
Date: 2000/01/06
Message-ID: <3874D97E.DD2EA90A_at_videotron.ca>#1/1
I would like some feedback on some database design that I'm having
problems with. Let's consider a table of distance between two cities:
Table tbl-distance
table tbl-city
tbl-city contain all cities considered in the database.
tbl-distance must contain distance between any two cities in the table.
One requirement for the database would be able to select a city from all
possible cities as a starting point then select another as a destination
point then obtain the distance between them.
I wonder, in such instance, how to populate the fields of the
tbl-distance table. Since all that is needed is the distance between
the cities there's no point in having (duplicate?) records such as:
Los-Angeles -> Huntingtonbeach: distanceLH
Huntingtonbeach -> Los-Angeles: distanceHL
If i do then, ins't it quite a lot of data repetition?
The other question is the way to provide city names in tbl-distance.
distance-id: number (primary key)
city-A : varchar
city-B : varchar
distance-between-them: number
city-id: number (primary key)
city-name: varchar
I welcome your inputs!
Michel Received on Thu Jan 06 2000 - 00:00:00 CET