I have 4 tables:
rooms(id, name, description)
clients(id, name, email)
cards(id, card_number, exp_date, client_id)
orders(id, client_id, room_id, card_id, start_date, end_date)
You must create the FK's in all columns that relate to other tables. In your case, create on:
In the case of MySQL it automatically creates indexes for these FK's.
On your select, I believe it can be the following:
SELECT * FROM orders INNER JOIN client on client.id = orders.client_id INNER JOIN cards on cards.client_id = client.id INNER JOIN rooms on rooms.id = orders.room_id
I do not know what columns you need, there is only you replace the
* by the columns you need, so SQL is faster.