1

I'm a noob when it comes to SQL or PHP and I'm a creating a rather simple car-sharing web site. The user can engage a trip someone created and can himself create a trip. Now I'm currently designing the SQL model, and I came across some doubts that maybe someone can help me with.

I want to save the user that created a given trip. That's easy, the tables "Trips" and "Users" have to be related, and the table "Trips" has the foreign key of "User" table, containing the user's ID that's related to a given trip.

The thing is, I wanted the user to log in and be given the chance to see: a) the trips he's engaged with; b) the trips he created. So, how can I separate data in order to have not only access to the user that created the trip, but also to users that are engaged in that trip (so that I can use all the info in the site)? What's the best way to arrange this efficiently in a SQL model, to save both the user that created the trip and the users that engaged it.

Improve this question

2 Answers 2

Reset to default
1

Without a lot more info, etc. couldn't you just create an EngageTrips table, and create an CreatedTrips table and hook a foreign key into those and then join the data once the function is run to bring that data for view to the end-user?

Also meaning control how the data is entered into each table at the data entry level based on criteria when the data is imported, etc.

Just a quick thought.

Improve this answer
0
0

Your model has two different relationships between User and Trip. They should be modeled separately. They have different cardinalities.

One is the "created" relationship which is one-to-many. This is modeled by the foreign key in Trip, as you say in your question.

The second is "engaged," which is many-to-many. This can be handled by creating a new table, known as an intersection table, that has foreign keys to both User and Trip. This table often acquires further columns as design progresses.

An alternative model is to omit the "created" foreign key and instead add a status flag to the intersection table. This flag will have two values - "created" and "engaged."

The choice is a compromise between ease of ensuring data integrity and ease of querying. If every trip must always have a created user, that is most easily enforced by a NOT NULL foreign key. I would suppose you would also want to ensure the total number of passengers is within the capacity of the vehicle, which is easier if they are all in one list. Other constraints will emerge as your design evolves.

From the information given my choice would be for a foreign key / intersection table design and a view to give all participants in the trip.

Improve this answer
1
  • Thanks for your reply. I'll think it further, but I think i'll do something in the lines of: An alternative model is to omit the "created" foreign key and instead add a status flag to the intersection table. This flag will have two values - "created" and "engaged."
    – TheTryHardz
    Aug 27, 2015 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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