1. I don't think it necessary to add begin_date , end_date fields
to this table.
2. for those whose prices change over time, price , begin_date , end_date and corresponding room types form a new table
.
3. query the table in table 2 based on the current or selected time. If you hit, you will use the price in table 2, and if you miss, you will use the default price in table 1.
4. such a room can have multiple price ranges, which can reach the customized price for a certain period of time, or even can be superimposed. For example, the price from July to October is 100, but the price of Qixi Festival is 10, just take the latest value, or mark the top value.

there are not many theoretical problems

try to extract the theoretical entity object and do it according to the business form

for example, the price follows the room type, the room type can be priced according to the date, and the price of the order placed by the end user is an order record, which can be abstracted into a concrete physical table!

order record complete record information:
user information, room type, room number, order date, discount, etc.

I won't be specific about specific queries, because I don't know your actual business