I have designed 3 tables as below:

Table: Country


  • country(name of country),
  • iso2_code,
  • iso3_code,
  • phone_code

primary key: country

Table: State


  • state(name of state),
  • country(fk from country table)

primary key: state, country

Table: City


  • city(name of city),
  • state(fk),
  • country(fk)

primary key: city, state, country

I have used composite keys as primary key for state & city tables. Is it bad design? Should I create auto-increment key for state & city tables & use them as primary key?

  • Be careful with the PK on City. Sometimes there can be more than one combination. Simcoe, Ontario in Canada is a good example, as there are two of them several hundred km apart
    – matigo
    Jun 9, 2021 at 12:02
  • @matigo, yes there can be two state or two city with same name. That is why I have kept primary key as city+state+country.
    – jasmin9891
    Jun 9, 2021 at 14:17
  • @mustaccio, composite primary keys bad practice does not give clear answer to above question because it says it depends on case by case we should either go with composite key & for some we should not but I am not sure for above case.
    – jasmin9891
    Jun 9, 2021 at 14:18
  • 1
    Even with city+state+country, you’d be looking at Simcoe+ON+CA twice; one that is just north of Lake Erie, and the other is darn near by James Bay
    – matigo
    Jun 9, 2021 at 14:21


Browse other questions tagged or ask your own question.