mysql database logic

My question is more of trying to understand what and how I can get something done. Here's the thing:

I got a job to build this application for a school to manage student bio data, work-out and handle student information and basic finance management.

Based on requirements I got from meets with my client, I have an ERD of a proposed MySQL Data开发者_如何学编程base with 23 different tables. The one part I would like to understand quickly is displaying data based on school terms. There are 3 terms in a year, each with its own summaries at the end of each term. At the end of 3 terms, a year has gone by and a student is promoted or demoted.

So my question is, how can I render my data to show 3 different terms and also to create a new year working out how to either promote a student or make the student repeat the class its in?

23 different tables? I'd like to see that model.

I don't think you should have one table per term. You'll have to keep adding tables every term, every year.

Sounds like a transcript table should have term and year columns that are incremented or decremented as a student progresses through. It should also have a foreign key relationship with its student: it's a 1:1 between a student and their transcript.

I would have a separate transcript table because I'd prefer keeping it separate from basic personal information about a student. A transcript would refer to the courses taken each term, the grade received for each, and calculate overall progress. If I queried for the transcript for an individual student, I should be able to see every year, every term, every course, every grade in reverse chronological order.