CODEWITHSUNDEEP
sqldatabase-design
Muhammad yasir
Muhammad yasir

Reputation:

Table Design and Relationship

Could someone suggest the best design for the following scenario? I have a database in which there is a table called City. This table has the following fields:

City id (Primary key)

City Name

State Id (which is linked to the State table)

My problem is I have 10 cities with the same name in one state. What will be the best design so I can represent one city name per id

Upvotes: 0

Views: 56

Answers (1)

TomTom
TomTom

Reputation: 62093

There is none. THe problem you have is:

I have 10 cities with the same name in one state

Now, here is the real issue. When you make a nice little entry mask, and the user types in the city name and state - you can not (and that is absolutely not) distinguish the 10 different cities (without additional data). They are absolutly identical. So there is no sense in multiple database entries.

So, to have them in the database, you need another separator, like a table with all the ZIP codes for a city. Because otherwise it makes no difference - you don't have 10 ID's because you can not anyway select one of them outside of a random selection in any data maintenance.

Upvotes: 3

Related Questions