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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments