0

I am new to Database.

I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile.

What is the most efficient way of doing so?

This is what I have:

Locality

  • PK UN LocalityID
  • UN LocalityName

Country

  • PK UN CountryID
  • UN CountryName

UserProfile

  • Username...Password...etc
  • FK CountryID
  • FK LocalityID
1

In Relational Database terms efficiency revolves around avoiding the duplication of data. The 3 tables that you show in your question seem reasonable provided:

  • You can have a Locality without having a Country
  • You can have a Country without having a Locality

This is actually quite a viable design, since in some parts of the world they may not define locations as you describe. (There are a lot of unique decisions made around the planet.) Of course you can generate an artificial name or description for your tracking purpose should that be necessary.

For example: There is no named locality, you could create a Locality like: "Island NW of Salty Creek"

If you intend for each Locality to belong to a single Country then you might benefit from a small change to add a CountryLocality table:

Locality

  • PK UN LocalityID
  • UN LocalityName

Country

  • PK UN CountryID
  • UN CountryName

CountryLocality

  • PK UN CountryLocalityID
  • FK CountryID
  • FK LocalityID

UserProfile

  • Username...Password...etc
  • FK CountryLocalityID

This is definitely a design and usage issue for you to decide on since the CountryLocality table adds some overhead. It depends on how strong the Country-Locality connection should be and whether this extra step is worth it for your application.

0

You may be better off eliminating the ID columns from Locality and Country tables. Otherwise, you can have duplicate or incorrect names because the IDs are different. The Country table could have every country in the world filled in easily and be used for lookups, not data entry. Only the Locality table would need some data entry for new records.

The ID column in the CountryLocality table will help with uniqueness of the combinations.

If you knew you had both, Locality and Country, for every UserProfile, you can eliminate the ID field for CountryLocality and just make a composite key of LocalityName and CountryName. This probably isn't the case and could make future updates tricky (for instances when places change names.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.