How to design a database with rare multiple values [closed]

Question

I have a "user" table, with many attributes including "nationality". the user can have multiple nationalities, so it sounds as if I have to design a new table to store this multiple value.

but what if I stored the first nationality in "user" table, and each of the remaining nationalities belonging to the same user would go to the nationality table.

I am thinking about this solution since it's pretty rare to have a user with multiple nationalities.. so, I can keep my design clean this way.

Is this feasible? is it a bad design?

Answer 1

If it was me who doing the design what I will do is create a table with name nationality each nationality have ID ...and since the the user have multiple nationality ...in the user table I will create two columns (foreign key) for nationality nationality1 and nationality2 referencing the nationality table .

the above scenario in case for two nationality .

for multiple nationality (unlimited) check the below scenario

the scenario for this situation will be similar to Customer and orders (one-to-many) ...one customers have many orders ...you will need 3 tables user nationality user_nationality in the user_nationality table you will the following columns id,user_id,nationality_id ...the user_id is foreign key column referencing the user_id in the user table and the nationality_id is a foreign key referencing nationality_id for the nationality table .

hope that will help .