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

Programming

21186 readers
245 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 [email protected]

founded 2 years ago
MODERATORS
[email protected]
[email protected]
[email protected]
[email protected]
1
What would be the best way to store the country of a user in SQL? (gregtech.eu)
submitted 2 weeks ago by [email protected] to c/[email protected]
17 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 @[email protected] in [email protected]

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?

top 17 comments
sorted by: hot top controversial new old
[–] [email protected] 0 points 2 weeks ago (1 children)

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

[–] [email protected] 1 points 2 weeks 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.

[–] [email protected] 0 points 2 weeks ago (2 children)

Store a jpg of the country's flag as binary

[–] [email protected] 1 points 2 weeks ago (1 children)

You're supposed to use PNG for images of that sort, you fucking barbarian.

[–] [email protected] 1 points 2 weeks ago

I'll use a gif with each frame being a different country flag. Then I can access them by frame index.

[–] [email protected] 0 points 2 weeks ago (1 children)

That's what you got emoji for.

[–] [email protected] 1 points 2 weeks ago

Well shit, that's weirdly viable.

[–] [email protected] 1 points 2 weeks ago (1 children)

I store iso country codes and use them as the primary key in a countries table.

[–] [email protected] 1 points 2 weeks ago

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.

[–] [email protected] 1 points 2 weeks ago (2 children)

There are ISO country codes.

https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes

[–] [email protected] 0 points 2 weeks ago (3 children)

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

[–] [email protected] 1 points 2 weeks ago

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.

[–] [email protected] 1 points 2 weeks ago

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.

[–] [email protected] 1 points 2 weeks ago (1 children)

not sure I understand the distinction between the "am I supposed to" and "maybe I could" parts?

You should create a table of all countries, you can just copy that from the above link. Then you reference that table with a foreign key in your users table.

[–] [email protected] 0 points 2 weeks ago (1 children)

Sooooo I copy paste every single country code and put it in a table?

[–] [email protected] 1 points 2 weeks ago

That's a perfectly valid approach, yes. We do exactly this, at work. It's pretty common, if not ubiquitous, to have your database schema consist of not only structure, but data as well. We call it static data, and it's all defined in deployable scripts, just like our tables and views are. If ISO makes changes to the dataset, then it's just a table update to match it. And ISO is nice about keeping backwards compatibility inb their standards.

Since this is not strictly your own data, you could also go with just storing the code value on your tables, and letting the UI layer do the lookup, either with built-in features of your language/framework, or with a static csv file, like you mention. You may not want to do this for static data that is entirely your own, like, say, a status or type enum, since it makes your database schema less-self-descriptive, and more prone to becoming invalid.

You could also set the country code up as a not-strictly-enforced foreign key, where your app will lookup additional info (E.G. the proper name) for a country code, if it's a standard one, but just skip that if it's not a standard one.

It's up to you what you think is most appropriate.

[–] [email protected] 1 points 2 weeks ago

This and nothing else. Had to deal with way too many APIs that would use some sort of homebrew schema.