I am building an application that will require a scheduler much like Cron. So I want to allow inputs for basically the same fields as Cron, such as minute, hour, day of week, day of month, month, etc. And allow people to input using things like [*], [1,2,3], [1-4], [1,2, 4-6].
My first thought was to carry input fields in the table and also carry searchable fields. So when they input 1,3-5 as an input I will interpret that as 01,03,04,05 in a searchable field. That works because that would be applied whenever they create or modify a record and it is searchable as to days, months, etc. For purposes of discussion hours and minutes will be absolute and only one set it allowed per record.
The issue I see with that is I will have to do some complex like clauses in the query and I am wondering about speed of queries and machine resources as the database grows, i.e
where month like '%07%' and dayofweek like '%05%' and day of month like '%01%'
So the question is would I be better off adding individual fields in the table for each day of the month, each day of the week, etc. and using a 0/1 type of indicator and doing my query on that? So that would be 31 fields for day of month, 12 fields for month and 7 fields for day of week. Those could also be individual tables that are joined on a common master id. And if it was done in individual fields, what would the indexes look like? Thanks for the guidance.
One other thought on this is to use 3 external tables, one for month, one for dow and one for dom that would only carry an id and day value and then do the query using inner joins. A more “vertical” vs “horizontal” approach. This question comes down to best practices for this type of design.
I already have both styles built and they both test out fine at this point with limited data. So everything is currently working, I just am curious what someone thinks that has more experience in the field than I do. Thanks
hahaha… It doesn’t actually require that but to answer your question i would add a field called every for the day of the month and day of the week. So every 3rd Tuesday, etc. Bet again, not a concern right now or in the foreseeable future. And I can see how if one needs to cover every combination that would be pretty complex. At least more so than what I am doing.
So you’re smarter than I am, how would you lay this out. I know in the past I have a tendency to try to “help” the database and more often than not get chastised to let it do it’s thing. It will do it better than I can.
[quote]So am I overthinking this?
[/quote]
possibly
i may appear to lack chalants here, but …
your table will likely always be small, possibly to the extent that any and every query will execute against an in-memory load of the entire table… the fastest possible execution
Thanks for the input and I can certainly live with that for now because as of now you are 100% correct. And in any event, I feel pretty comfortable that if at some future point it does require further evaluation, the way I have the data structured I will have several options without a huge impact to the application code itself.
