user2513485 user2513485 - 1 year ago 62
MySQL Question

MySQL multiple tables relationship (code opinion)

I have 4 tables:

rooms(id, name, description)
,
clients(id, name, email)
,
cards(id, card_number, exp_date, client_id)
and
orders(id, client_id, room_id, card_id, start_date, end_date)
.

The tables are all InnoDB and are pretty much simple. What I need is to add relationships between them. What I did was to assign
cards.client_id
as a Foreign Key to
db.clients
and
orders.client_id
,
orders.room_id
and
orders.card_id
as Foreign Keys to the other tables.

My question: is this way correct and reliable? I never had the need to use Foreign Key before now and this is my first try. All the Foreign Keys are also indexes.

Also, what's the easiest way to retrieve all the information I need for
db.orders
?
I need a query to output: who is the client, what's his card details, what room/s did he ordered and what's the period he's checked in.

Can I accomplish this query based on the structure I created?

Answer Source

You must create the FK's in all columns that relate to other tables. In your case, create on: cards.client_id, orders.client_id, orders.room_id, orders.card_id 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download