CODEWITHSUNDEEP
sqldatabase
Mtok
Mtok

Reputation: 1630

What should be the table structure for this situation

I am working on a social platform like web site. I'm not sure what should be the table structure of this situation;

I will have trips that users will create. And users can add users to the trips that they traveled with.

So;

>One Trip
  >Many users

Should I create the with just two columns;

>tripID
>userID

and adding new row for every new user for the same trip ?

Or do you have any other opinion ?

Thanks for any advice.

Upvotes: 0

Views: 48

Answers (2)

Andrew Kuklewicz
Andrew Kuklewicz

Reputation: 10701

You have several different relationships here:

  • A trip has a creator
  • A trip has many users that go on the trip
  • A user goes on many trips

This means a one-to-many for trips to user for creating trips (a user can create many trips, a trip has one creator).

You also have a many-to-many, as a user can go on many trips, and trips can have many users, so you need a join table for that.

I would name the join table trip_users, as that is the two joined entities in alphabetical order, which is a standard in some places:

trips

  • id
  • creator_id (refers to the user that created the trip)

users

  • id

trip_users

  • user_id
  • trip_id

Upvotes: 2

xQbert
xQbert

Reputation: 35323

You have three distinct objects here. A user can be on many trips A trip can have many users. By definition this is a many-to-many relationship. For most RDBMS it needs to be resolved though an associative table. (which is the 3rd object)

TABLE: user

  • userID
  • userInfo...

TABLE: Trip

  • TripID
  • TripInfo...

TABLE: userTrips

  • userID
  • TripID
  • userTripInfo...

Upvotes: 1

Related Questions