CODEWITHSUNDEEP
mysqlschema
user23750
user23750

Reputation: 268

Confusion in creating table design

I am using Mysql and I have two tables-

BusDetails
    +-------+-----------+
    | busId | BusName   |
    +-------+-----------+
    | 1     | A TRAVELS |
    | 2     | B TRAVELS |
    | 3     | C TRAVELS |
    +-------+-----------+

AreaDetails
+--------+----------+
| cityId | cityName |
+--------+----------+
| 1      | ABC      |
| 2      | DEF      |
| 3      | GHI      |
| 4      | JKL      |
+--------+----------+

Now I have to create third table which will map bus table to city table. Suppose busId 1 stops at cityId 2 and 3 and bustId 2 stops at cityId 1 and 4. To create this scenario I have 2 options-

first option-
+-------+--------+
| busId | areaId |
+-------+--------+
| 1     | 3,2    |
| 2     | 4,1    |
+-------+--------+

second option-
+-------+--------+
| busId | areaId |
+-------+--------+
| 1     | 2      |
| 1     | 3      |
| 2     | 1      |
| 2     | 4      |
+-------+--------+

In future when there will be large number of records then which table will give better performance and why ?

Upvotes: 1

Views: 44

Answers (3)

Barmar
Barmar

Reputation: 781721

The first option is poor because comma-separated lists do not get indexed. If you want to find all the busses in area 2, you would have to use 

SELECT busID
FROM bus_areas
WHERE FIND_IN_SET('2', areaID)

This will have to perform a full table scan, parse the areaID column on each row, and test whether 2 is a member of the resulting array.

With the second version you can do:

SELECT busID
FROM bus_areas
WHERE areaID = 2

If you have an index on areaID, this will be extremely efficient.

If you wanted to know how many busses are in each area, it's easy with the second option:

SELECT areaID, COUNT(*)
FROM bus_areas
GROUP BY areaID

With the first option it would be more cumbersome:

SELECT cityID, COUNT(*)
FROM areaDetails a
JOIN bus_areas ba ON FIND_IN_SET(a.cityID, ba.areaID)
GROUP BY cityID

This will be very inefficient because it has to perform M*N FIND_IN_SET operations, and as I explained above this cannot be indexed. Notice that I had to join with the areaDetails table because there's no way to enumerate all the areas in the comma-separated lists in SQL.

Upvotes: 2

Avinash Kalola
Avinash Kalola

Reputation: 113

Dear Second Table Is Better For All Reason Baecause At long time you have big data second type in save so many rows but better for getting report easy in easy for SQL query easy. you can all type join easy.

Upvotes: 0

Nishant Kumar
Nishant Kumar

Reputation: 2169

The answer depends upon your use.

Although first option is not recommended but if you have very large data and you are not planning to perform wide range of Database operations (probably for self or small project) you can use it.

Second options has it's own advantage and recommended by relational model. It will give you more flexibility and scalability as this minimize redundancy.

Upvotes: 0

Related Questions