I need to store a weekly schedule as compact as possible. The schedules are in increments of 15 minutes. In a 24-hour period that would give me 96 timespaces. Midnight is 00, 0600 is 24, 1800 is 72 and 2345 is 95. I need to store 7 days of information per SO record … Search speed depending on the space, is it more efficient to create a record with 14 small columns (Time start and end time for each day) or 1 column that concatenates filled-in numbers? (IE 2472247224722472247224722472)
There could be millions of records, so I want to try to reuse common schedules. This means that when someone creates a schedule, I want to look for the schedules that have already been created to find a match and use the ID of the match if I can find one. If not, I will create a new record. Would the use of 14 AND statements in smallints be faster than a character field search? Is there an even better way to do this?
MS SQL Server by the way.