I am making this hotel reservation program and i'm in a dilemma.
I have the users table that is basically
id
identifier
password
realName
cellphone
email
The rooms table
id
type
price
And the reservations table
id
checkin
checkout
room_id
nights
total_cost
The problem is that a single user in a single reservation can ask for multiple rooms with multiple check ins and outs.
What would be the best approach to achieve this? I was thinking of splitting the various rooms with different reservation ids and then make some kind of workaround to relation them.
I think your data structure is fine as far as it goes. You have two choices.
The first is to relax your language. Don't say that "a single user in a single reservation can ask for multiple rooms with multiple check ins and outs". Instead say, "a single user can make multiple reservations at the same time". Just changing this language fixes your conundrum.
If you really have to tie things together, I might suggest having an column that groups reservations made by a single user together. This could be a full-blown entity, which would have a foreign key reference to another table. Or, it could simply be an identifier, such as the first reservation in the series or the user id with a date/time stamp. I'm not sure that a full blown entity is needed, but you might find it useful.