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?
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
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.
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.
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.
Sooooo I copy paste every single country code and put it in a table?
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.
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.
If you can’t figure out how to get a foreign table into your database let someone who knows databases do it for you
How exactly you create that table is up to you of course, I don’t know enough about your project setup.