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?


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 .

  • But what if one user have more than two nationalities.. there is no restriction on the maximum number of nationalities a user can have. Also it sounds like the nationality2 in your design would remain null most of the time, and I don't see it deserving to affect my table shecma in this fashion. – AliSmart Jun 15 '17 at 8:49
  • @AliSmart I have edited the answer and added a scenario for unlimited nationality ...basically it's similar to the scenario for one customer can have many orders ...(one-to-many) . – Osama Al-Banna Jun 15 '17 at 9:19
  • I marked it as an accepted answer even though it's a many-to-many relationship representation according to your design. Thank you! – AliSmart Jun 15 '17 at 10:15
  • @AliSmart thanks ...all the best to you...yes you are right in your case it's many-to-many – Osama Al-Banna Jun 15 '17 at 10:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.