Composite primary key plus a separate (surrogate) id column for foreign key references

candidate-keydatabase-designerdprimary-keysurrogate-key

I am trying to build a database about locations like so:

Country → Region → Town

Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users.

Region may not apply to every location and so a blank string will be allowed (hence the many to many link table).

This is the best design I have come up with:

The town_id column is set to AUTO_INCREMENT & UNIQUE. It is then used as a foreign key reference in other tables.

The town column on its own is not UNIQUE as you may have the same town name in multiple places.

Is the towns table correct?

The reason I ask is because it just seems odd to me to have the town_id as the reference, however this cannot be included or used as the primary key (because if it did, it would cause duplicates)?

I would welcome feedback if this approach is correct.

Best Answer

I would recommend using it (town_id) as the only primary key (provided my first comment is true about the increment and uniqueness), and the Region/Country IDs should only be foreign keys. This would allow you to have duplicate town names.

You might want to put a unique index on country_id, region_id, town, as I'm pretty sure there's no political subdivision that has two towns of the same name, and it would reflect that business logic.

Related Solutions

Should every table have a single-field surrogate/artificial primary key

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

Pure intersection tables. If there is no risk of the intersection being the target of a foreign key and if there is little or no risk of the intersection attracting independent attributes (i.e. something other than FK's to the two parent tables) then you can get away with using the combination of FKs as the PK with fair confidence.
Lookup tables with static business keys. If you have a lookup
table with a unique business key which is fixed externally to your
business and which has zero chance of ever changing for any
practical purpose, then using the business key directly can make
things simpler. An example might be a list of state or province
codes or a list of ANSI standard numbers, etc.
Tables containing data consolidated from multiple, independent sources. If your system has many sources of data that must be shoehorned together into a single table, say at head office, then sometimes you need a compound key that includes the source system key value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal. You can have keys that are alphanumeric surrogates. These could include:

Situations where you need to merge data from multiple, independent sources. To avoid key collisions you might use GUIDs instead of IDENTITY keys.
Situations where you are forced to use non-numeric key representations. Let's say you've got a license plate database. Your key could be the alphanumeric value instead of a pure number.
Situations where some external requirement forces you to apply compression to your key value. Instead of using 10 digits for an int32 you can use six base 36 digits.

Why most of the time yes? The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table. Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell. Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept. You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.

Postgresql – How to design a database for continents, countries, regions, cities and POIs

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J

Best Answer

Related Solutions

Should every table have a single-field surrogate/artificial primary key

Postgresql – How to design a database for continents, countries, regions, cities and POIs

Related Question