CODEWITHSUNDEEP
phpmysqldatabase-designnormalization
Haroldo
Haroldo

Reputation: 37377

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

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?

Upvotes: 2

Views: 220

Answers (3)

orderedanalog
orderedanalog

Reputation: 41

I think Randy's answer is good... another idea to add to his would be to use a calendar table like:

COTTAGE_CALENDAR

  • cottage_id
  • date
  • reservation_id

Add a single row for each day of booking and make the cottage_id and date fields a combined primary key....then you'll prevent overbooking... (adding the reservation_id to the COTTAGE_RESERVATION table that Randy built, for the relational data link)....

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

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.

Upvotes: 1

Randy
Randy

Reputation: 16677

COTTAGE


cottage_id,
address?,
other info...

USER

user_id,
name,
other info...

COTTAGE_RESERVATION

cottage_id,
user_id,
from_date,
to_date

Upvotes: 7

Related Questions