Best way to implement many-to-many relationship in MySQL

Question

I have 2 tables, users(~1000 users) and country(~50 countries). A user can support many countries so I am planning to create a mapping table, user_country. However, since I have 1000 users and 50 countries, I will have a maximum of 50000 entries for this table. Is this the best way to implement this or is there a more appropriate method for this?

If this is the best way, how can i add a user supporting many countries to this table using only one SQL statement? For ex:

INSERT INTO user_country(userid, countrycode)
VALUES ('user001','US'),
('user001','PH'),
('user001','KR'),
('user001','JP')

Above SQL statement will be too long if a user supports all 50 countries. And I have to do it for 1000 users. Anyone have any ideas the most efficient way to implement this?

Answer 1

Personally I'd do the insert based on a select:

INSERT INTO user_country SELECT 'user001', countryid from countries WHERE countryid IN ('US', 'PH', 'KR', 'JP');

You need to adapt to your column names.

The alternative of storing list of countries in a single column usercountries varchar (255) as US,FR,KR and so on would be possible as well - but you'd lose the ability to select users based on the country they support. In fact you don't lose it - but

SELECT * FROM users WHERE usercountries like '%KR%';

Is not a good query in terms of index usage. But as you only have 1000 users a tablescan will be mighty quick as well.