CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
dev1234
dev1234

Reputation: 5706

Table with one to many relationship or many to many?

Below given are how the tables look like,

Place (id, name) i.e 1,Work | 2,Home | 3,College [lookup table]

UserPlace (id, placeId, contryId, userId) i.e 1,2,1,12 it means, id is 1, place is work, contry is Australia. user id is 12

Transport (id, name) i.e 1,Bus | 2,Train | 3,Cycle [lookup table]

UserPlaceTransport (userPlaceId, transportId, distance)

sample data to this table, belonging to one user.

1,1,10km - UserPlace id is 1 which is work, Transpory way is by Bus. distance is 10km

1,2,20km - UserPlace id is 1 which is work, Transpory way is by Train. distance is 20km

1,3,70km - UserPlace id is 1 which is work, Transpory way is by Cycle. distance is 70km

2,3,50km - UserPlace id is 2 which is home, Transpory way is by Cycle. distance is 50km

4,3,40km - UserPlace id is 3 which is college, Transpory way is by Cycle. distance is 40km

According to this structure, there is a small confusion in UserPlaceTransport table. which is, there is also a possiblity of getting values for all 3 userPlaces together. which means, a user has reached for all three (work, college,home) this many km's. i.e by bus 20%, by train 60% and by Cycle 20%

But current the table structure matches only for othet way, i.e for work, by bus 10%, by train 90%.. for college - by bus 50%, by cycle 50%

so i am wondering how to expand the DB table inorder to store this kind of a value too. my approach is to take two seperate tables, UserPlaceTransportBasic & UserPlaceTransportAdvanced so, UserPlaceTransportAdvanced will be as same as earlier while UserPlaceTransportBasic will look like following,

UserPlaceTransportBasic (userId, transportId, distance) so this clears the confusion.

i want to know is there any better sollution to handle this kind of a situation. i mean without creating two seperate tables but with only one table.

Thanks

Upvotes: 0

Views: 73

Answers (1)

simon at rcl
simon at rcl

Reputation: 7344

You sais this:

According to this structure, there is a small confusion in UserPlaceTransport table. which is, there is also a possiblity of getting values for all 3 userPlaces together. which means, a user has reached for all three (work, college,home) this many km's. i.e by bus 20%, by train 60% and by Cycle 20%

I don't see this: UserPlaceTransport specifies the UserPlace and the Transport so there is no duplication or confusion.

There is another problem with this table though: it has one end of the journey, but not the other: if the UserPlace is Work is the distance from college or from home? This can be solved by adding another field pointing to the second UserPlace involved. A better solution would be adding a Journey table (id, FromUserPlaceid, ToUserPlaceId) and using the Journey table instead of the UserPlaceTransport table.

Or is your problem that a journey may involve multiple methods of transport? If so, take the distance and TransportId out of the UserPlaceTransport (or Journey) table and add table UserPlaceTransportSection (id, UserPlaceId, TransportId, distance). you could leave the total distance in the UserPlaceTransport table if you wanted, but that's a bit of premature optimisation so I wouldn't.

Does that help?

Upvotes: 1

Related Questions