Databases
StackOverflow2013
Postsdbo
PODatabase design: Low overhead solution for managing daily inventories / capacities?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase design: Low overhead solution for managing daily inventories / capacities?
    text
    copied!<p>Here is the scenario: (MySQL 5.1+, PHP, Apache)</p> <p>I am planning a SaaS application that will let CLIENTS visit SHOPS and book TRIPS. (ALL CAPS are entities). SHOPS offer TRIPS but they only have a certain number of EMPLOYEES to guide the TRIPS (a transactional record). Essentially it is an issue of managing a daily capacity for each SHOP based upon the number of available EMPLOYEES. What is the best DB design solution for delivering this functionality in a way that incurs the lowest amount of overhead?</p> <p>Here is a simplified view of the database entities:</p> <pre><code>table.clients client_id (pk, ai) table.shops shop_id (pk, ai) table.employees employee_id (pk, ai) shop_id (fk) table.trips trip_id (pk, ai) client_id (fk) shop_id (fk) trip_date (date) </code></pre> <p>SCENARIO 1<br> I could run a query on TRIPS for every request when a user wants to view the calendar, like:</p> <pre><code>SELECT COUNT(*), trips.trip_date, trips.shop_id FROM trips WHERE shop_id=1 GROUP BY trips.trip_date, trips.shop_id </code></pre> <p>SCENARIO 2<br> Create a summary table that stored info on every day but this strategy seems nightmarish with overhead issues. For instance, imagine that there are 1000 shops each booking 1000 trips per 365 day year <strong>and</strong> the table should store info for the next 2 years (830 days). It seems like that would 1/ create a huge summary table (830,000 rows) that would 2/ be queried 1,000,000+ times per year (1000 shops * 1000 trips per shop). When a CLIENT booked a TRIP it would increment the number (or when a trip was cancelled the number would decrement) which would effectively create a daily inventory/capacity.</p> <p><strong>So, my question is this:</strong> Which method is the best? Or is there a better way to accomplish this?</p> <p>Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload