What would be the best way to store the country of a user in SQL? (gregtech.eu)

submitted 3 days ago by lena@gregtech.eu to c/programming@programming.dev

42 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

[-] ulterno@programming.dev 1 points 2 days ago* (last edited 2 days ago)

It will depend upon other things you might want to do with it in the future.
If you want the record to stay "correct" in the future, you might want to have a separate entry for nationality information, which will be a 1-to-1 mapping with the other table you make for it. Why? ...

in the nationality information, along with the country code (for which you want to allow 3 character codes for forward compatibility), the date at which the the information was given in the form
if possible, also include the date at which the nationality was provided (as in, provided to the user by the nation)

This should help determine what kind of change may occur in case of changes in the political landscape in the future, without you requiring to re-ask the form-filler.

Inspired by https://flightaware.engineering/falsehoods-programmers-believe-about-aviation/
And I am going to consider this stuff every time I make a database schema.

Stuff in the real world is subject to change, so instead of only storing the provided data as you asked for, make a ledger for events. And someone submitting a forms, will be another event.

This way, you get the flexibility to verify the provided data in the future, using information that you will have in the future, but don't have at the time the data was provided.

Of course, this is only needed if it is needed.

this post was submitted on 12 Jun 2025

17 points (100.0% liked)

Programming

20902 readers

121 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

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