MySQL DataBase design Question

Question

I'm thinking of the best way to design a database given the following situation.

• There are over 1000 hotel room units.
• Each hotel room unit has a list of about 100 individual types of items with a qty attached to them. The inventory is standard for each hotel room. But the qty of each item can be changed.

For example 3 coffee mugs, 2 towels etc.

I also need to track lost or damaged items for each unit...

what would be the best way to design the database?

Have a table of each unique item, its qty, its property id, its lost and damaged items for each property ID?

Is there a better way to do it?

Answer 1

Room[roomID, roomNumber, roomSize]
Item[itemID, itemPrice, itemName]
RoomItem[roomID, itemID, quantity, damaged]

Have a many-to-many relation with room and items (called RoomItem). Can then be specific about the quantity for an item in a room and a damaged item in a room.

Answer 2

Table of rooms Table of items Table of contains that references rooms and items:

Rooms
Number  ID
101     1
102     2

Items
Name    ID   NumberLost   NumberDamaged  
Lamp    1    3            0
Chair   2    0            1

Contains
RoomID  ItemID  Quantity
1       1       1
1       2       3
2       1       1
2       2       4