1

I need a table City for my database model, I have two possible designs:

City:
    id_city   PK
    city
    country


City:
    city
    country
    PRIMARY KEY(city, country)

I am wondering what would be more appropriate. In case 1 I could insert a city twice with a different primary key...Right? The case two appears to be more correct...isn't? What you recommend me?

rendon
  • 2,293
  • 1
  • 17
  • 23
  • Both could work. For efficiency what kind of queries will you be running ? I would go with option 2. – Kassym Dorsel Dec 09 '11 at 15:18
  • 1
    You can still use a surrogate key as your primary key, but keep the uniqueness constraints, although in this case you effectively have a natural key and a redundant surrogate key. Perhaps it's easier to reference the surrogate key, but it's a tradeoff you must consider when designing a db. See http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql for examples (mysql specific.) – Austen Holmes Dec 09 '11 at 15:23
  • Also, google natural vs surrogate keys for dialog. Technically, according to db theory, you should choose natural keys whenever available, bc the key should uniquely describe the data (when you look at a surrogate key, it doesn't tell you anything about the data.) In practice, I don't think anyone follows this, I'm not sure why. Perhaps it's for performance. – Austen Holmes Dec 09 '11 at 15:27

4 Answers4

3

You might try something with a postal code. Think about it: how many Springfields are there in the US? That's just ONE country. At one time there were 3 cities named Hahn in West Germany. The relationship between city and postal code is one (city) to many (postal code).

cybermama
  • 31
  • 1
  • Postal code is fraught with the same possible issue, in America there are zip codes which another country may use (eg Australia uses 4 digit postcodes) - Re reading the question also some Cities (eg London) have multiple postcodes. – Stephen Dec 09 '11 at 16:02
2

There can be more than one city with the same name in a particular country.

For example, in the United States, there are cities named Miami in both Florida and Ohio and there are cities named Portland in Oregon and Maine.

Martin Gordon
  • 35,754
  • 6
  • 55
  • 53
2

I would suggest the following:

City:
    id_city   PK
    city
    country
    UNIQUE Constraint(city, country)

Why?

  1. Need more software controls/logic to have a string key vs. an integer/number
  2. DB operations on integer/number keys is faster
  3. More support for auto generation of such key (in db engines and JPA for example)
  4. If for some weird reason the city name changes it'll break the database integrity.

Just my 2 cents.

javydreamercsw
  • 5,060
  • 13
  • 56
  • 97
  • 1
    It won't quite work. In Germany, to pick just one country, there are several cities named Auerbach, Bergen, Erlenbach, Steinbach, and so on. You're on the right track with a unique constraint, though. Joins on integers are faster that some strings, but if a foreign key references {city, country} you don't need the join at all. Changes to city names happen; cascading updates or a stored procedure that inserts, updates, and deletes work well. – Mike Sherrill 'Cat Recall' Dec 10 '11 at 10:57
  • I guess is due to my ignorance on those foreign countries. It'll work for USA and most of Americas that I'm aware off. Maybe adding an additional field like province? – javydreamercsw Dec 10 '11 at 20:57
  • Oh, it won't work in the USA. There are at least 15 different Jacksons, 14 different Franklins, Springfields, and Clintons, 13 different Salems. In fact, among cities in the USA, the names of about 1200 appear in more than one state. – Mike Sherrill 'Cat Recall' Dec 11 '11 at 00:58
  • That's why I mentioned adding province(/state). – javydreamercsw Dec 12 '11 at 15:20
1

in a country you could have several cities with the same name. Look here Id' suggest using a city_id int as primary key and adding zipcode to the columns so then you can validate by that aswell.

Community
  • 1
  • 1
Andres
  • 1,953
  • 6
  • 37
  • 67