How to store a table with hourly rates?

Question

MySQL

Hi everybody.
Has anyone come across or have ideas.
There are tariffs - an hour for renting a vessel, tariffs vary for days of the week, hours, and there may be different categories of passengers.
There will be more than one ship, there will be many of them, and each will have its own tariff scale.
You need to do a search by ships and display the cost of rent for the selected period.
What is the best way to store and work with data?
There is an idea to make a separate table in which it will be scheduled by the hour for the year ahead, but its volume turns out to be quite large (about 8760 rows per ship). Maybe there is a better way?

Answer 1

See how this is done in pms systems (hotels, and indeed any booking of resources). This will be the most correct. almost all variations of situations are taken into account. But there will be more entities. Tariffs, services, service packages, calendars, price change orders in tariffs, discounts, dynamic pricing, customer types, segmentation, etc. All this affects the final amount in the end.

Answer 2

If there are few requests, then you can store intervals (tariff, type, start_date, end_date, set(days_of the week), start_time, end_time, price_hours). But queries can be complex and lengthy.

Answer 3

Each hour has its own attribute in the table (i.e. 24 attributes, and their values ​​in the table are prices), because in queries this data will be substituted with a specific value, which means it can be done by generating the query itself (this will not affect its speed execution), but there will be no overloaded structure, extra indexes and overhead to work with them = plus to the speed and ease of development.