Programming @programming.dev Lena @gregtech.eu 4w ago

What would be the best way to store the country of a user in SQL?

Cross-posted from "What would be the best way to store the country of a user in SQL?" by @lena@gregtech.eu in !learn_programming@programming.dev

I use Gorm. This is the current code:

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type Env struct {
	DB     *gorm.DB
	Logger *log.Logger
}

type User struct {
	ID           uint
	Username     string
	Name         string
	Email        string
	PasswordHash string
	Country      string //should probably be a foreign key of another table
}

func initDB() {
	env := &Env{}
	db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{})
	if err != nil {
		fmt.Printf("Error opening database: %v", err)
		return
	}
	env.DB = db
	env.DB.AutoMigrate(&User{})

}

func main() {
	initDB()
}

As you can see in the comment in the code, I assume the best way would be to have a table of countries and then assign each user to one via a foreign key. However, it seems a bit cumbersome to manually create a list of all countries. Is there a better way to do this?

31 comments

There are ISO country codes.

https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes
- This and nothing else. Had to deal with way too many APIs that would use some sort of homebrew schema.
- Am I supposed to make an SQL statement that puts these country codes into a table, along with the country's name? There's probably a better way. Maybe I could make a new entry for every unique country a user is from
  
  Why do you need to store the name of a country in the database? Frontend can take the country code and display a full name on its own, and do it in a localized way too.
  
  You don't need these in a table the same way you don't need a table for something like true and false. Two characters is enough to deduce all the information.
Store a jpg of the country's flag as binary
- This is the only real way to do it, the other solutions involve "standards" which more often than not aren't all encompassing. Make sure that any user input of a country is just them uploading the jpg of their home country without any sort of validationbecausee everyone is loyal to their home country.
- That's what you got emoji for.
  
  Well shit, that's weirdly viable.
- You're supposed to use PNG for images of that sort, you fucking barbarian.
  
  I'll use a gif with each frame being a different country flag. Then I can access them by frame index.
- Better use FHD png to keep the quality.
- This should be the standard :)
An important question no-one has asked yet is, What do you need that info for?
I store iso country codes and use them as the primary key in a countries table.
- Same for using an "almost non-changing" standard, i.e. either ISO or RFC. And write a script to update the data or tables if something wrong changes (like Russia disappearing because it has been invaded by Belgium), you never know what might happen politically.
GPS coordinates. Much more efficient than storing country, province, city, address, and postal code. Let the front end handle the rest.
- GPS? Absolutely insufficient. What about the people on the ISS? Or when the moon base is established? Ever thought of that? No. You think only of yourself.
  
  Fine, we'll add altitude as well.
  
  Simple, add additional columns for the frame of reference (e.g. Earth) and elevation. You could even store space coordinates using Sun as a reference point (though you would need to update data regularly for spacecraft as they move of course).
- Terrible idea for a few reasons.
  
  The example in the OP does not need anything but the country. GPS coordinates are less efficient than ISO codes
  
  GPS coordinates don't map 1:1 to countries or even street addresses. There are infinite different coordinates for each address, and it's very non-trivial to match one to another. Comparing whether two records with country codes are in the same country is trivial. Doing the same with two GPS coordinates is very difficult.
  
  GPS coordinates might be more exact than accurate. This is a surprisingly common issue: you start out only needing a country, so you put some arvitrary GPS position (e.g. the center of the country) into the GPS coordinates. Later a new requirement arises that means you now need street addresses. Now all old entries point so some random house in the middle of the country, and there's no easy way to differentiate these false locations from real ones.
  
  I guess you meant that as a joke, but people are really doing this and it leads to actual problems.
  
  I saw a news report a while ago about something like that being done in a database for people with outstanding debt. If the address of the debtor wasn't known, they just put "US" in the form, and the program automatically entered the centre of the US as the coordinates.
  
  Sucks for the family that lives there because they constantly get threatening mail and even house visits from angry lenders who want their money back. People even vandalized their house and car because they believed that their debtors lived in that house.
  
  Yep, meant it as a joke.
I'd store it as a string (ISO code), pretty much every programming language has icu data for country names.

If you need more data later, it's very simple to migrate.
I'm a fan of CountryCode being a string column that's a FK to a reference table of Country values.

Same for State/Province.

31 comments