CODEWITHSUNDEEP
mysqldatabasedatabase-designmany-to-manyrelational-database
dats
dats

Reputation: 123

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

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?

Upvotes: 1

Views: 296

Answers (2)

TAM
TAM

Reputation: 1741

From the point of view of database design, a table like your user_country is the only sensible way to go. 50000 records are a breeze for MySQL, and having them together with the appropriate indexes will open up all ways of future use for those data.

As far as I can see, this is unrelated to the problem of many large SQL insert statements. No matter how you represent the data in the database, you will have to write statements containing, for each user, a list of countries.

This is a one-time action, right? So it doesn't need to be a masterpiece in software engineering. What I sometimes do is load the raw data in Excel, line by line, then write a formula that "calculates" the appropriate SQL statement for the first line, and copy this formula for all lines. Then throw all these statements at the database. Even if there are tens of thousands of them, it's not much effort.

Upvotes: 1

Jan
Jan

Reputation: 13858

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.

Upvotes: 0

Related Questions