16
What would be the best way to store the country of a user in SQL? (gregtech.eu)
submitted 1 day ago by lena@gregtech.eu to c/programming@programming.dev
38 comments fedilink hide all child comments

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?

you are viewing a single comment's thread
view the rest of the comments
[-] owenfromcanada@lemmy.ca -2 points 1 day ago

GPS coordinates. Much more efficient than storing country, province, city, address, and postal code. Let the front end handle the rest.

[-] squaresinger@lemmy.world 5 points 1 day ago* (last edited 1 day ago)

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.

[-] owenfromcanada@lemmy.ca 3 points 1 day ago

Yep, meant it as a joke.

[-] squaresinger@lemmy.world 3 points 23 hours ago

In that case, woosh me. Just wanted to make sure nobody takes that as an actual advice.

[-] bjoern_tantau@swg-empire.de 6 points 1 day ago

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.

[-] ulterno@programming.dev 2 points 19 hours ago

GPS: Galactic Positioning System

Just suppose you are not selling the software outside of the galaxy and you will have to update your database (and transform and migrate all the data) when the 2 galaxy positioning system is formed.

[-] bjoern_tantau@swg-empire.de 2 points 18 hours ago* (last edited 18 hours ago)

Ugh, this will be chaos when we crash into Andromeda. I'm not looking forward to the next billions of years.

[-] ulterno@programming.dev 1 points 16 hours ago

Just complete your KRAs beforehand and hand over the project to the Junior Dev, before then.

[-] deadcream@sopuli.xyz 2 points 1 day ago

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).

[-] owenfromcanada@lemmy.ca 4 points 1 day ago

Fine, we'll add altitude as well.

[-] ulterno@programming.dev 1 points 19 hours ago

altitude

Which ones?

[-] TehPers@beehaw.org 5 points 1 day ago

You should also include the standardized name of the body the coordinates are relative to. Need to be able to differentiate between lat/long on Jupiter vs on Earth (where lat/long are much more "crunched" aka more precise with shorter floats).

This will be important if intelligent extraterrestrial life is found, or when Musk ships himself to Mars for the good of humanity.

[-] owenfromcanada@lemmy.ca 1 points 1 day ago

Out of scope for the project, we'll flush it from the backlog and include it in another epic.

[-] vext01@lemmy.sdf.org 2 points 1 day ago

What about alternative universes? 🤣

[-] owenfromcanada@lemmy.ca 1 points 1 day ago

They have their own databases, no additional info required.

this post was submitted on 12 Jun 2025
16 points (100.0% liked)

Programming

20885 readers
101 users here now

Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!

Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.

Hope you enjoy the instance!

Rules

Rules

  • Follow the programming.dev instance rules
  • Keep content related to programming in some way
  • If you're posting long videos try to add in some form of tldr for those who don't want to watch videos

Wormhole

Follow the wormhole through a path of communities !webdev@programming.dev

founded 2 years ago
MODERATORS
snowe@programming.dev
Ategon@programming.dev
MaungaHikoi@lemmy.nz
UlrikHD@programming.dev