Mysql : should i put my data into 1 table or split per user?

Question

This data is for a holiday cottage's simple accommodation calendars. The data is simple and stores dates when each cottage is booked.

The cols would be cottage_id, booked_from_date, booked_until_date and I would expect around 60 rows per user per year * 200-300 users.

I should put this is one table right?

Answer 1

Yes, this should be placed in a single table (for all cottages).

You will run into a whole world of hurt when you start placing data that should be in a single table (that can be seperated by a defining type/id) into seperate tables.

Just think of how you are to write a query to retrieve availability accross all units, for a given date, accross 10-40 or more units?

Normalizing the table into a MANY to MANY structure is perfectly normal, seperating Cottages from Users and linked by a table CottageUsers with the booked dates.

Answer 2

COTTAGE

cottage_id,
address?,
other info...

USER

user_id,
name,
other info...

COTTAGE_RESERVATION

cottage_id,
user_id,
from_date,
to_date