0

I have two tables namely 'City' and 'Travels'. City Table will have list of cities in each row with city_id as the Primary Key and Travels Table will have list of travels in each row with travels_id as the Primary key.

Scenario:

One City has many travels for service and One Travels has many city destinations as their service. (i.e) Many-To-Many Relationship in the Table.

         city table                     travels table
+------------+------------+      +-------------+----------------+
|    city_id |  city_name |      | travels_id  | travels_name   |
+------------+------------+      +-------------+----------------+
| 1          | coimbatore |      | 1001        | KPN travels    |
| 2          | mumbai     |      | 1002        | Garude travels |
+------------+------------+      | 1003        | SRT travels    |
                                 +-------------+----------------+
  city_travels join table
+-------------+----------+
| travels_id  | city_id  |
+-------------+----------+
|  1001       |   1      |
|  1001       |   2      |
|  1002       |   1      |
|  1003       |   1      |
|  1003       |   2      |
|  1003       |   3      |
+-------------+----------+

This is how the Many-to-Many relationships between tables are made. But my doubt question is that if either of city table or travels table row increases the city_travels join table row will increase enormously.

Is there any other way to achieve Many-to-Many relationship between tables. I searched a lot but i couldn't find any other solutions.

Please let me know, if there any other options or trick to achieve this. Moreover please comment if there is any wrong in this question.

Thanks a lot in advance

  • 3
    "the join table row will increase enormously" No problem. That's what DBMS are designed for. Tables with millions and billions of rows. – ypercubeᵀᴹ Oct 16 '16 at 18:23
1

When you get to 100 million rows, we might need another chat. Meanwhile, this describes the optimal schema for many:many.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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