What is the better way to organize primary keys in database?

Question

My database holds two tables, one is distance matrix of cities, the other holds cities. My first structure was like this:

• City
  • Uuid
  • Name
  • Latitude
  • Longitude

and

• Distance
  • FromCityID
  • ToCityID
  • DistanceLength

UUID is a primary key of the CITY table, FromCityID and ToCityID reference CITY each as foreign key, and both are composite primary key of the DISTANCE table because the distance between two cities should be unique.

But then a realised that I don't want to use UUID and auto-increment as primary keys because I upload data in this database from XML, which holds cities and distances. And as distances may include not only those cities that mentioned in current XML but any previously stored city from database.

I need a system of IDs that is the same across the database and XML. Latitude / longitude seems the best option so I changed my tables to this:

• City
  • Name
  • Latitude
  • Longitude

and

• Distance
  • FromCityIDLatitude
  • FromCityIDLongitude
  • ToCityIDLatitude
  • ToCityIDLongitude
  • DistanceLength

Latitude and Longitude are a composite primary key of the CITY table; FromCityIDLatitude / FromCityIDLongitude and ToCityIDLatitude / ToCityIDLongitude reference CITY each as foreign keys, and all four columns are composite primary key of the DISTANCE table.

But it's a bad design to use 4 columns as a primary key. What is the best thing in this case?

Answer 1

To answer the question you asked in the title,

What is the better way to organize primary keys in database?

Blind keys, either integers or universally unique identifiers, are almost always better as keys. They never need to change. Data elements may or may not change.

In your particular case, the latitude and longitude of a city aren't likely to change. However, if you receive a latitude / longitude correction for a city, you now have to make the correction in two tables.

Answer 2

I disagree with this statement:

But it's a bad design to use 4 columns as a primary key.

A bad design is one which doesn't do what you need it to or one that allows inconsistencies in the database. In your case I don't see a problem with a four column primary key as long as we make one assumption. That is, your main access route to this table will use every column in the primary key. If this is the case then it's fine; I would put the entire table into a unique index and put a separate unique constraint on the four columns of your key.

The problem with a four column index is when you try to access the table by the fourth leaf. You probably won't use the index at all. If it then becomes necessary to regularly do an index look-up on the fourth leaf you have to add another index, etc. It's possible to end up with a ridiculously over-indexed table.

The way round it would be to stagger the loading. Don't load your XML data directly into your main database table. Load them into a secondary table and run a process to see if this city already exists. If it does then don't add it. If it doesn't then generate a new surrogate key and do a CROSS JOIN to add all the new records to DISTANCE.

Answer 3

Don't forget the "physical" design of your tables. For the distance matrix, consider using an Index-Organized Table (IOT), and compress the columns.

See the discussion of a similar question (regarding a distance table) on AskTom here:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:239614547000#52902724002052