Re: database's table design with similar foreign keys

From: Jim Gross <jmgross_at_worldnet.att.net>
Date: 2000/01/07
Message-ID: <38759B5B.49A14F6D_at_worldnet.att.net>#1/1

Michel,

I would first propose you change the design of your tables as follows:

Table tbl-city

---

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

Table tbl-distance

---

city-id(1) : number (primary key)
city-id(2) : number (primary key)
distance-between-them: number

Note that you now have an identifying relationship between any two city-ids in tbl-city and the distance table. Thus, a reflexive join of two instances of tbl-city with tbl-distance on city-id will give you the two city names and the distance between them. Unfortunately, this will require you to have all combinations of two cities populated in tbl-distance including all the redundant pairs as well as distance records where city-id(1) and city-id(2) are the same (and so the distance is 0) to avoid getting null results from valid combinations of cities.

However, there is at least one way to eliminate the redundant pairs and same-city distance records. You would build your distance queries to return a UNION of the two cities in one order and the two cities in the other order. One half of the UNION would always return a null and the other half would return the distance. The "same-city record" case where city-id(1) and city-id(2) are the same would return null for both halves of the UNION. The query could be coded to detect this and return 0 for the distance.

Another way to eliminate the redundant pairs and same-city distance records would be to have your application programming coded to always sort the two city-ids so that the lower one comes first in the query and so that same-city-id combinations automatically produce zero distances without querying the database. You would then populate your distance table only with the records where city-id(1) is less than city-id(2).

A third solution might be to write database rules and/or triggers to handle the cases where the redundant or same-city distance record does not exist, but this would give you a database product specific solution that could not be easily migrated to another database platform.

Finally, if none of the above workarounds appeal to you, go with the simpler solution and populate the duplicates and same-city records. You probably wouldn't notice any difference in performance and I'm sure the redundancy won't use up that much additional disk space.

Good luck.

JMG Michel Jean wrote:

> 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 Fri Jan 07 2000 - 00:00:00 CET