I am currently making a management software for a small artistic school: a small intranet in PHP / MySQL.

I'm trying to draw database architecture before I start coding because I don't want to make bad choices right from the beginning.

My diagram can be found below.

The software requirements are as follows:

  • Register a student for a school year and assign them to one or more courses
  • Register a student for a unique event (Concert, Masterclass, internship ...)
  • Register teachers and assign them to one or more courses
  • Manage the student financial side, the number of payments and the total cost of the training
  • And later there will be other needs like saving marks, schedules, and maybe other things ...

I tried to transcribe it all on my diagram, but here are some explanations:

  • A student (student table) is unique and can register for several years. This table contains all the generic information (name, email, phone ...)
  • A student registration (enrollment table) can follow several courses (course table) and several events (events table)
  • A student can pay for their training in one or more installments (funding table)
  • A course is given by a single teacher (trainer table) and an event is given by a single speaker (speaker table)

Is this the best way to keep a student's information over several school years? Should I add some optimizations?

Database diagram


2 Answers 2


UPDATE - I added some optimization, as suggested by @Matigo

Students, Speakers and Trainers all share common elements : lastname, phone number, address and more.

So it’s better and easier to have only one People table, but we keep the Students, Speakers and Trainers tables as subtables.

DB Diagram after update

database diagram after update


I would start by adding an address table and a studenttoaddress table with the type of address.

Then create an address control that saves edits to address. This can be reused in the future.

I am not a fan of putting TableName_ in front of every column. It's part of the table and making generic controls or data objects that run on column names is harder.

If your table ID is named ID there is a lot of reusability that can be achieved. That's the first glance.

  • Take a look at this answer where I talk about why using ID as the column name is a terrible idea.
    – Hannah Vernon
    Commented Feb 15, 2022 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.