I would organize it differently. My table (opening_times) would be
just open_at, close_at plus some sort of belongs_to column that
points to the owner (i.e. store owner). Then I’d have
days_opening_times table that had a weekday (probably just the index,
sunday = 0, monday =1) and an id column that pointed to the id of the
opening_times table.
Then it’s a matter of stepping through the list of weekdays in the
days_opening_times table. If two consecutive weekdays have the same
opening_times id then it’s a range. If the range two days, you say
“monday, tuesday - 9am - 6pm”. If the range is more than two days,
you say “monday - friday: 6am -9pm”.
Clearly this is just off the top of my head and may be fatally
flawed… but it might be an alternative.
You could then select all days for a given store, ordered by start
time, close time.
Count the number of times each range occurs and decide how many
different days until you just list them all individually. If it’s
just Thursday they are open late, you’d see 4 x 8am-4pm, and 1 x
8am-6pm. If three are different, maybe list all 5 days.
I would think about the simplest solution. The times seem essentially
to
be up to 7 “arbitrary” strings. Unless you need to perform some type of
actual date calculation against the open-hours policy, then this data is
for
display/reference only. So, treat it like display-only user-input data.
For editing the list I would even go so far as to treat it like 7 static
textboxes that the owner can update any time they want. (I’m expecting
up to
7 different hours, one for each day) At display, include the rows that
actually contain content. You can think about wether to store empty
data
for all 7 rows or only those that contain data (I would go with
data-content
only).
Keep it simpler unless you have a further reason to calculate based upon
this data.
Sorry guys. But you are totally missing the simplest solution.
It would be like this:
table store_times
store_id :int
opening_times :text
and into that textfield would come the data:
“Monday 6:00 AM - 10:00 PM
Tuesday 7:00 AM - 10:00 PM
Wednesday - Sunday 6:00 AM - 10:00 PM”
You have not said anything here that really tells me that you need
some extensive system to handle this and do some complex calculation
to find out opening times. Remember that if it’s not needed then it’s
just there to increase the chances of a bug.
However if there is some other reason for why you need to have all the
dates seperate it would help to know why.
That’s pretty much a solution I posed to my client, but unfortunately,
and I kind of left this out of the original message – they don’t want
the UI to be a text field. The client wants to minimize input errors by
having them select the times from like a time_select helper. So I could
still store it as text, but showing it from the database would be a
nightmare.
Thanks for all the suggestions above everyone!
I’m still thinking about the best solution. Hmm…
Jón Borgþórsson wrote:
Sorry guys. But you are totally missing the simplest solution.
It would be like this:
table store_times
store_id :int
opening_times :text
and into that textfield would come the data:
“Monday 6:00 AM - 10:00 PM
Tuesday 7:00 AM - 10:00 PM
Wednesday - Sunday 6:00 AM - 10:00 PM”
You have not said anything here that really tells me that you need
some extensive system to handle this and do some complex calculation
to find out opening times. Remember that if it’s not needed then it’s
just there to increase the chances of a bug.
However if there is some other reason for why you need to have all the
dates seperate it would help to know why.
I would think about the simplest solution. The times seem
essentially to be up to 7 “arbitrary” strings. Unless you need to
perform some type of actual date calculation against the open-hours
policy, then this data is for display/reference only. So, treat it
like display-only user-input data.
I like this idea.
As you’ve said, “unless you need to perform some type of actual date
calculation” it would work very well and be quite simple to
implement. Two concerns: standardizing the data inputted, and as
mentioned, not being able to do calculations–while you might not see
any need for them now, maybe you will in the future. Having a
thousand entries to re-enter *might make you wish you did it
differently.
Oh ok… I thought it was a case of when sometimes you forget the simple
things.
Btw. To complicate your life even further. The WILL contact you next
december because they need to add the holiday opening times to the
system and other specific dates.
I really dislike clients. Can’t live with them. Can’t take their money
if they don’t exist.
So I’m thinking I need to have a one (business) to many (hours)
association where they can add multiple, and name it.
The display formatting is still irking me! Grrrr!!!
Jón Borgþórsson wrote:
Oh ok… I thought it was a case of when sometimes you forget the simple
things.
Btw. To complicate your life even further. The WILL contact you next
december because they need to add the holiday opening times to the
system and other specific dates.
I really dislike clients. Can’t live with them. Can’t take their money
if they don’t exist.
