title: First-time database design: am I overengineering?
body: <h1>Background</h1>
  
  <p>I'm a first year CS student and I work part time for my dad's small business. I don't have any experience in real world application development. I have written scripts in Python, some coursework in C, but nothing like this.</p>
  
  <p>My dad has a small training business and currently all classes are scheduled, recorded and followed up via an external web application. There is an export/"reports" feature but it is very generic and we need specific reports. We don't have access to the actual database to run the queries. I've been asked to set up a custom reporting system. </p>
  
  <p>My idea is to create the generic CSV exports and import (probably with Python) them into a MySQL database hosted in the office every night, from where I can run the specific queries that are needed. I don't have experience in databases but understand the very basics. I've read a little about database creation and normal forms.</p>
  
  <p>We may start having international clients soon, so I want the database to not explode if/when that happens. We also currently have a couple big corporations as clients, with different divisions (e.g. ACME parent company, ACME healthcare division, ACME bodycare division)</p>
  
  <p>The schema I have come up with is the following:</p>
  
  <ol>
  <li><strong>From the client perspective:</strong>
  
  <ul>
  <li>Clients is the main table</li>
  <li>Clients are linked to the department they work for
  
  <ul>
  <li>Departments can be scattered around a country: HR in London, Marketing in Swansea, etc.</li>
  <li>Departments are linked to the division of a company</li>
  </ul></li>
  <li>Divisions are linked to the parent company  </li>
  </ul></li>
  <li><strong>From the classes perspective:</strong>
  
  <ul>
  <li>Sessions is the main table
  
  <ul>
  <li>A teacher is linked to each session</li>
  <li>A statusid is given to each session. E.g. 0 - Completed, 1 - Cancelled</li>
  <li>Sessions are grouped into "packs" of an arbitrary size</li>
  </ul></li>
  <li>Each packs is assigned to a client</li>
  </ul></li>
  </ol>
  
  <p>I "designed" (more like scribbled) the schema on a piece of paper, trying to keep it normalised to the 3rd form. I then plugged it into MySQL Workbench and it made it all pretty for me:<br> (<a href="http://maian.org/img/schema.png" rel="noreferrer">Click here for full-sized graphic</a>)</p>
  
  <p><a href="https://i.stack.imgur.com/JU8cW.png" rel="noreferrer"><img src="https://i.stack.imgur.com/JU8cW.png" alt="alt text"></a><br>
  <sub>(source: <a href="http://maian.org/img/schema.png" rel="noreferrer">maian.org</a>)</sub>  </p>
  
  <h2>Example queries I'll be running</h2>
  
  <ul>
  <li>Which clients with credit still left are inactive (those without a class scheduled in the future)</li>
  <li>What is the attendance rate per client/department/division (measured by the status id in each session)</li>
  <li>How many classes has a teacher had in a month</li>
  <li>Flag clients who have low attendance rate</li>
  <li>Custom reports for HR departments with attendance rates of people in their division</li>
  </ul>
  
  <h1>Question(s)</h1>
  
  <ul>
  <li><strong>Is this overengineered or am I headed the right way?</strong></li>
  <li>Will the need to join multiple tables for most queries result in a big performance hit?</li>
  <li>I have added a 'lastsession' column to clients, as it is probably going to be a common query. Is this a good idea or should I keep the database strictly normalised?</li>
  </ul>
  
  <p>Thanks for your time</p>