I need to save users with some data that may be same for multiple users (for the example – country).
Let’s assume that I don’t know all the countries at the beginning, so I will need to add them to the DB when user is create.
I can define my schema like this:
CREATE TABLE users(id int, country text)
but the country can be the same for most of the users (schema is not normalize) so I want to define my schema like this:
CREATE TABLE users (id int, countryId int) CREATE TABLE country (id int, country text)
but then I have other problem:
When user is trying to register he supply country and not the id
so to create the user I need to read the country id and if not exists create a new country.
The obvious issue is that if I have two process they both can insert the same country twice.
To fix this I wanted to add a unique constraint to country column on country table.
I searched to see if there are solutions that maybe better then this and I was sure that I will find this solution either but all the solutions were different for example:
Why adding the unique constraint is bad solution?