holidays in mysql for multiple stores

Question

I have a table in a MySQL database with shops and days. I need to check if the shop is open on that day.

Before checking if a shop is open following this method: Find next time business is open; mysql hours calculation I need to check whether the shop is on some holiday before checking if it's open or not.

Is it good to have a table with :

Shop ID | Days

xxx | number,number,number, etc..

that the application (written in PHP) queries checking whether the [yday] is contained in the Days string (a comma separated values string)? The Shop ID will be a foreign key in the Shops table, and the application will always know in advance which shops to run this query for, not the other way around.

I have found no computationally cheap or elegant solution around so far. It may be a very simple question, but the solutions are soo many I'm having a hard time figuring out the different performance issues.

Edit 1

Considering also the performance implications of the query as follows, having considered @GordonLinoff answer as the best solution:

$ShopId = //a POST or GET $day = //a POST or GET $query = mysqli_query($con,"SELECT day FROM table WHERE ShopId = '$ShopId' AND day = $day"); if(mysql_num_rows($query)== 0){ echo "Shop open"; } else{ echo "Shop closed"; } mysqli_close($con);

Gordon Linoff · Accepted Answer · 2013-12-24 15:49:16Z

2

You would have a single table with a row for each shop and each holiday. So a row would be:

ShopId Day xxx number xxx number xxx number

You can then easily check inclusion in this table using SQL. And, it will be quite efficient if you have an index on (ShopId, Day).

edited Dec 24, 2013 at 15:49

answered Dec 24, 2013 at 15:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3132821 Over a year ago

Apologies if I take advantage of your knowledge. When will creating tables per each shop represent a problem (let's say thousands of shops)? Also, why would I build the index on both columns rather than only on the day, after all, the shop ID will be exactly the same on the whole table.

Tim Dearborn Over a year ago

You will be creating one table, not one table per shop.

Dan Bracuk Over a year ago

You don't create tables for each shop. You create one table. If you don't know about database normalization, I've heard good things about the book, Database Design for Mere Mortals.

Gordon Linoff Over a year ago

@TimDearborn . . . I didn't realize the original phrasing was ambiguous. One table is what you want.

user3132821 Over a year ago

@GordonLinoff I've updated my question. I misunderstood your answer and I was surprised. One table makes sense. Is the performance the best considering the query in the question? We can then close this thread as solved.

|

Collectives™ on Stack Overflow

holidays in mysql for multiple stores

Edit 1

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit 1

1 Answer 1

6 Comments

Linked

Related