Dim Dim - 2 months ago 8
MySQL Question

Proper way to store data in SQL

I have basic SQL question for something I`ve done wrong. So I wish to ask what is the proper way to accomplish this.
I have Room table. Room have room size, name and wall color. The room have furniture table, it can have many furniture. What is the right way to connect multiple furniture to room. For example:

Room Table
roomID: 5
size: 15
wallColor: Blue
haveFurniture: 2,4,5 //This is the part I`m not sure about

Furniture Table
furnitureID 2
type: table
color: wood

Furniture Table
furnitureID 4
type: closet
color: wood

etc...


So, what is the right way to connect furniture to room. The user may create multiple rooms and multiple furniture

Answer

Create a many-to-many (n-m) relation table to join the two. Based on your notation:

RoomHasFurnature Table
roomID 5
furnitureID 2

RoomHasFurnature Table
roomID 5
furnitureID 4

RoomHasFurnature Table
roomID 5
furnitureID 5

You'd then use a join to pull that data out. Here's a crude example:

SELECT * FROM Room
LEFT JOIN RoomHasFurnature USING(roomID)
INNER JOIN Furniture USING(furnatureID)

This would give a row per furniture item. You can squash it down in some SQL dialects with a GROUP_CONCAT()

SELECT r.*, GROUP_CONCAT(f.type) FROM Room AS r
LEFT JOIN RoomHasFurnature USING(roomID)
INNER JOIN Furniture AS f USING(furnatureID)