This is something that I find pretty hard to do the easy way, since I'm a newbie.
I will try to describe what I'm looking for and since my own thoughts are not
that easy to follow I thought I will make a fictive Car Rental instead. So you
guys will understand what it's all about.
Let's say I have a small business where people can rent a car.
I have 5 employees in a table called USERS (user_id, username, password, phone)
I have a table with CARS (car_id, model, booked, registered_by)
And a third table for BOOKINGS (book_id, registered_by, booked_car, customer)
So my site will start logging in a user (where I also start a session for that username)
Then there will be a list of all the bookings in the MySQL
Here I want to see booked_car, model, registered_by and also the cars that are available.
If a car isn't in the list (which is true the first times of course), the user will be
able to click a button to add a car to the database.
Now I started to be unsure...
When a user adds a car it won't show up in the table, since it's not booked.
Should I make an insert into the BOOKINGS at the same time as the car is added,
that says it's linked to CARS but mark it as unbooked.
Or should I just add the car and in the table showing all the bookings make something
that also shows cars that are not booked?
I just don't understand how to make the connections here. And which way is the right
way to do it here. Or the best. And smartest?
I guess it's best to have some kind of connection between all the 3 tables with an
id telling every table how they connect.
Like users.user_id = bookings.registered_by
and users.user_id = registered_by
and cars.car_id = bookings.booked_car
Or is that too many? It's so hard to plan all the parts the first time.
Since a car can be booked several times as well. I might want to keep old bookings
alive as well. For future use. Who knows? They say one should plan a long time ahead.
I hope you people understand this complicated description of my problem.
But I think a car booking would be pretty easy to use as an example.