Hello. Designed Hi-load DB. There is no experience in this. Now schedules are stored in a separate table: company_id, day, hour. It is proposed to put them in the field of the table of companies in the form of 168 bits (24 * 7). Will it work faster? What type of data to choose?

  • It depends on what you do with this data. If you have queries like "find companies working on weekends" then a complete table scan to check this bit field is guaranteed. indexes will not save. And if you just show on the screen for an already selected company, it will come down. And the existing table in any case is necessary to alter. Well, the companies do not work at 13, 15, 18, we work, at 14, 16, 17 there is no. It is better to store intervals. By the way, the lunch break from 13:15 to 14 or from 13:30 to 14:30 is a common phenomenon and it is impossible to describe it with your current approach or bit field - Mike
  • What kind of problems do you have with the current scheme? on what operations? what operations do you have more or what is more important in speed? how many companies do you have in the database? Work with the schedule is conducted on one company or on many at once? Are you sure that it will always be enough for you to store the schedule with an accuracy of 24 * 7? those. can not start with a half-hour, there is no special schedule on holidays and pre-holidays? - 4per
  • what's with the loaded systems? - etki
  • It is necessary to determine whether the company is working at the moment. Those. in principle, this field can be cached and use bit operations for verification. - Artem

1 answer 1

  • company_id,
  • dateA - the beginning of the working interval
  • dateB - the end of the working interval

So you can specify any gadgets with dinner 13:30 and the end of the working day on Friday at 16:45, instead of the usual 17:00.

In general, when planning data storage, you need to know what will be the requests in the future. Why do we store data? What do we want from them?

Your clarification of how the data will be used will help us to more accurately provide an answer.