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

Programming

20902 readers
122 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
17
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
41 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?

top 41 comments
sorted by: hot top controversial new old
[–] tal@lemmy.today 61 points 3 days ago (2 children)

There are ISO country codes.

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

[–] einkorn@feddit.org 27 points 3 days ago

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

[–] lena@gregtech.eu 5 points 3 days ago (2 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

[–] deadcream@sopuli.xyz 29 points 3 days 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.

[–] schnurrito@discuss.tchncs.de 7 points 3 days 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.

[–] lena@gregtech.eu -1 points 3 days ago (4 children)

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

[–] JakenVeina@midwest.social 1 points 1 day 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.

[–] locuester@lemmy.zip 1 points 1 day ago* (last edited 1 day ago)

Heya, long time dev here.

If you’re not expecting millions of rows, you could just use the country code directly in the user table and not use a foreign key/table at all. Just an idea.

However, if you want the country table, i would find this list in a csv format (or copy paste into goog sheets or such and massage it) and then use a tool or write a quick script to ingest it into the db. If you’re doing that you should design the country table to have id, code, name. It can be used for populating dropdowns or autocomplete or such too.

Over-engineering is a thing; keep it simple. If you’re doing this mainly as an exercise to learn, over-engineering can be ok - just understand that it doesn’t HAVE to be done any particular way, and there is no “right” way. Design is subjective and varies based on the needs of your software.

[–] Reddfugee42@lemmy.world 2 points 2 days ago

If you can't figure out how to get a foreign table into your database let someone who knows databases do it for you

[–] schnurrito@discuss.tchncs.de 4 points 3 days ago

How exactly you create that table is up to you of course, I don't know enough about your project setup.

[–] pineapple_pizza@lemmy.dexlit.xyz 33 points 3 days ago (5 children)

Store a jpg of the country's flag as binary

[–] squaresinger@lemmy.world 6 points 2 days ago (1 children)

That's what you got emoji for.

[–] owenfromcanada@lemmy.ca 4 points 2 days ago (1 children)

Well shit, that's weirdly viable.

[–] squaresinger@lemmy.world 2 points 2 days ago

It really is.

[–] Dumhuvud@programming.dev 3 points 2 days ago (1 children)

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

[–] Kissaki@programming.dev 2 points 2 days ago (1 children)

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

[–] JackbyDev@programming.dev 2 points 1 day ago

Make it webp so it's webscale!

[–] ExperimentalGuy@programming.dev 10 points 3 days ago

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.

[–] dukatos@lemmy.zip 2 points 2 days ago

Better use FHD png to keep the quality.

[–] PolarKraken@programming.dev 2 points 2 days ago

This should be the standard :)

[–] bleistift2@sopuli.xyz 19 points 3 days ago

An important question no-one has asked yet is, What do you need that info for?

[–] spacemanspiffy@lemmy.world 2 points 2 days ago

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.

[–] wise_pancake@lemmy.ca 10 points 3 days ago (1 children)

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

[–] 6nk06@sh.itjust.works 7 points 3 days 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.

[–] 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.

[–] rikudou@lemmings.world 4 points 3 days ago

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.

[–] owenfromcanada@lemmy.ca -3 points 3 days ago (2 children)

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 2 days ago* (last edited 2 days ago) (1 children)

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 2 days ago (1 children)

Yep, meant it as a joke.

[–] squaresinger@lemmy.world 3 points 2 days 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 3 days ago (3 children)

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.

[–] deadcream@sopuli.xyz 2 points 2 days 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).

[–] ulterno@programming.dev 1 points 2 days ago (1 children)

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 2 days ago* (last edited 2 days ago) (1 children)

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 2 days ago

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

[–] owenfromcanada@lemmy.ca 4 points 2 days ago (3 children)

Fine, we'll add altitude as well.

[–] TehPers@beehaw.org 5 points 2 days ago (1 children)

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 2 points 2 days 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 2 days ago (1 children)

What about alternative universes? 🤣

[–] owenfromcanada@lemmy.ca 1 points 2 days ago

They have their own databases, no additional info required.

[–] ulterno@programming.dev 0 points 2 days ago

altitude

Which ones?