database's table design with similar foreign keys

From: Michel Jean <mrjean_at_videotron.ca>
Date: 2000/01/06
Message-ID: <3874D97E.DD2EA90A@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

---

distance-id: number (primary key)
city-A : varchar
city-B : varchar
distance-between-them: number

table tbl-city

---

city-id: number (primary key)
city-name: varchar

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. Both field city-A and city-B contains data that are part of the same set of data. Should I have the table populated with the names of the cities or have a foreign key to tbl-city.city-id?

I welcome your inputs!

Michel Received on Thu Jan 06 2000 - 00:00:00 CST