CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
Alessandro Corradini
Alessandro Corradini

Reputation: 499

Best way to store users address into european database?

I would like to storing user's address (country-region-town) into a database. This database is the core of a european app. I have a table of countries and foreach country, I have two tables, one for regions and one for towns/cities. So, what's the best way to storing user's address into database?

I could create different tables foreach country and rename them "users_italian", "users_french" with columns "idUsers" and "idTown" and I should connect them with the respective town table. It's simple but this mode does not convince me because there would be too many tables.

alternatively I could create a table "users_country" with "idUsers" and "idCountry" (foreign key) and I should memorize region and country into another table "users_address" with "idUsers", "region" and "town", but with string date type, example:

users(idUsers:1, email:[email protected], psw:secrethashedpsw, token:secrettoken)

country (idCountry:1, Country: "United Kingdom")

users_country (idUsers:1, idCountry:1(refer to United Kingdom))

users_address (idUsers:1, region:"England", town:"London", zip: SW1H 0TL (example))

Also this mode does not convince me because the database would not be normalized, but it's simple to realize it! and the query select it's simple too. I could create different tables about "users_region", "users_town" and "users_zip", but the storing with strings does not convince me.

Anyone can help me?

Upvotes: 0

Views: 523

Answers (1)

Rick James
Rick James

Reputation: 142298

One table per country? NO!

Have a single users table. It would have

Plan A: address and either city+region+country.

Plan B: address plus id linking to (normalized) table of cities. Cities contains city+region+country.

Normalizing country, for example, adds complexity without adding any benefit.

Upvotes: 1

Related Questions