Database design to get as many stats as possible

Question

I have to structure a MySQL database for work and haven't done that in years. I'd love to get some ideas from you. So here's the task:

I have a couple of "shops" that have, depending on the day of the week and year, different opening hours, which could change further down the line. The shops have

space for a given amount of people (which could change later as well).

A few times a day we count the amount of people in the shop.

We want to compare the utilized capacity between shops. I myself would like to use dc.js to be able to get as much stats as possible from the data.

We also have two different methods of counting our users:

1. By hand. Reliable, but time consuming.

2. Light barrier. Automatic, but very inaccurate.

I'd like to get a better approximation of the usercount using the light barrier data and some machine learning algorithm.

Anyway, do you have any tips on how to design the DB as efficiently as possible for my tasks. I was thinking:

SHOP

• Id

• Name

OPENINGHOURS

• Id

• ShopId

• MaxUsers

• Date

• Open

• Close

MANUALUSERCOUNT

• Id

• ShopId

• Time

• Count

AUTOUSERCOUNT

• ID

• ShopId

• Time

• Count

Does this structure make sense (at all and for my tasks)?

Thank you!

Answer 1

For an application of this size, I see no problem with this at all. Except what does "time" column in usercount tables refer to ?