Background

I am a newbie CS student and that i work part-time for my dad's small company. I haven't any experience of real life database integration. I've written scripts in Python, some training in C, but nothing beats this.

My father includes a small training business and presently all courses are scheduled, recorded and accompanied with an exterior web application. There's an export/"reviews" feature but it's very generic and that we need specific reviews. We do not have the actual database to operate the queries. I have been requested to setup a custom confirming system.

My idea would be to produce the generic CSV exports and import (most likely with Python) them right into a MySQL database located at work every evening, where I'm able to run the particular queries that are required. I do not have experience of databases but comprehend the very fundamentals. I have read just a little about database creation and normal forms.

We might start getting worldwide clients soon, and so i want the database not to explode if/when that occurs. We presently possess a couple large companies as clients, with various divisions (e.g. ACME parent company, ACME health care division, ACME bodycare division)

The schema I've develop may be the following:

  1. In the client perspective:
    • Clients may be the primary table
    • Customers are from the department they work with
      • Departments could be scattered around a rustic: HR working in london, Marketing in Swansea, etc.
      • Departments are from the division of the company
    • Divisions are from the parent company
  2. In the classes perspective:
    • Periods may be the primary table
      • An instructor is related to every session
      • A statusid is offered to every session. E.g. - Completed, 1 - Cancelled
      • Periods are arranged into "packs" of the arbitrary size
    • Each packs is designated to some client

I "designed" (a lot more like scribbled) the schema on the sheet of paper, attempting to keep it normalised towards the 3rd form. Then i blocked it into MySQL Work bench also it managed to get all pretty for me personally:
(Get more information at full-sized graphic)

alt text

Example queries I'm going to be running

  • Which clients with credit quit are inactive (individuals with no class scheduled later on)
  • What's the attendance rate per client/department/division (measured through the status id in each session)
  • The number of classes includes a teacher been on per month
  • Flag clients who've low attendance rate
  • Custom reviews for HR departments with attendance rates of individuals within their division

Question(s)

  • Is overengineered or am I headed the proper way?
  • Will the necessity to join multiple tables for many queries create a large performance hit?
  • I've added a 'lastsession' column to clients, because it is most likely likely to be a typical query. Is advisable or must i keep your database strictly normalised?

Interesting time