MySQL or PHP tables exploded during business hours

Question

I need to keep working hours for 200+ unique businesses (and growing). Each business has a different clock, and some for lunch. In addition, a business may open at 8 am one day and 9 am another. My initial thought was to build a MySQL table to handle this, but then I decided to save it in one cell and hack the results. Blast it, I would save it something like this ...

// "8:00-17:00|9:00-18:00|8:30-12:30,13:30-17:30|8:00-17:00|8:00-17:00|8:00-11:00||" // For Day Data: Explode "|" // For Open/Close Data: Explode "-" // For Converting to am/pm: Explode ":" 

In one cell store, arrays process data pulled from the clientData table (which has already been looped). With a separate MySQL table, I look at 7-14 rows per client (starting at 1400 - 2800 rows), depending on how it is built. One way is better than another. How do you approach this?

Thanks!

DESIRED EXIT

 <div class="businessHours" itemscope itemtype="http://schema.org/LocalBusiness"> <time itemprop="openingHours" datetime="Mo 8:00-17:00"><strong>Monday:</strong> <span>8:00am-5:00pm</span></time> <time itemprop="openingHours" datetime="Tu 9:00-18:00"><strong>Tuesday:</strong> <span>9:00am-6:00pm</span></time> <time itemprop="openingHours" datetime="We 8:30-12:30,13:30am-17:30"><strong>Wednesday:</strong> <span>8:30am-12:30pm, 1:30pm-5:30pm</span></time> <time itemprop="openingHours" datetime="Th 9:00-18:00"><strong>Thursday:</strong> <span>9:00am-6:00pm</span></time> <time itemprop="openingHours" datetime="Fr 8:00-17:00"><strong>Friday:</strong> <span>8:00am-5:00pm</span></time> <time itemprop="openingHours" datetime="Sa 8:00-1:00"><strong>Saturday:</strong> <span>8:00am-11am</span></time> <time itemprop="openingHours" datetime="Su "><strong>Sunday:</strong> <span>Closed</span></time> </div> 

Answer 1

I would definitely use a table. You can structure it in two ways:

Vertical table: id, Store ID, Day of the week, Clock

Horizontal table: id, store identifier, M, T, W, TH, F, S, SU

There are pros and cons for each, but after this approach you can quickly list all the store’s hours only for a specific day. Otherwise, you will have to disassemble each line and blow it up to the position of the day, or use a tax-based instruction.

Answer 2

I will pose a counter argument for the prevailing logic here. If, as you say, you do not expect that at some point in the future you will need to request specific data about the business hours, I would suggest that the entire data collection was simply the property of the business and could be stored in a consistent manner. This will make it possible to record and retrieve data for each business more optimally (eliminating more complex queries across multiple tables).

I would suggest that you can also store data in a serialized format (perhaps JSON, perhaps your own PHP serialization format), so when searching, you can simply de-serialize the data and work with it.

Now, if you think that you will need to request information about business hours (i.e. display information, for example, who is open now, who is open Saturday, etc.), then I would advise most other commentators on this topic and normalize data.