SQLTeam.com | Weblogs | Forums

Need advice for a Big database/table selection and design

We have a property management system using PostgreSQL as a database hosted on AWS RDS. This PMS is being used by 10 properties currently. It is working fine today.

Now, there is a scope that very soon we will onboard 1000 plus properties in the system.

So, I am worried having huge data in the tables will have an impact on the performance for search operations, getting data using Joins.

One hotel can have 10-20 Room Types based on the size. Every room types can have 15-20 rate plans. Then each room type is going to have price stored for the whole year. There would be approx 40k entries. Now if these needs to be pushed to different channels like bookings com, agoda etc then the values will be 40K * 7( channels currently in the system.)

Total (1 property): 280, 000

For 1000 properties: 280,000,000

Now, this table has join with 4 tables. So, I need advice for the best approach?

Thank you in advance.

Do you have a pre production test environment?

@yosiasz Not yet. I want to know what would be the best approach to design the DB?

I thought you already have a database? Can you show us what design you already have like an ERD?