Is Temporary Table Good for Importing Temporary Data into Database?

database-designMySQLtemporary-tables

i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well they performed in an exam. After i have finished with my queries i don't need the temporary student data anymore. I am trying to think of an elegant way of how to store this temporary student data in my database. The only thing i found out that in my opinion could be a solution to my problem is a temporary table, however i am not sure. My tables have the following structure.

Student
id(PK), matriculation_number, first_name, last_name, course_of_study, email

Exam
id(PK), student_id(FK), grade

Temporary student list
matriculation_number, first_name, last_name

All in all what i am trying to achieve here is import the temporary student list into a table, then compare it with the student table, using the matriculation number, in order to get the id of the student and finally use the student id to access all the exam results for that particular student.

What do you think is a temporary table a good solution for the above problem? If not how would you go about it?

I am using MySQL as RDBMS.

Best Answer

In your case, temporary tables could prove to be a challenge.

Specifically, temporary tables (created by CREATE TEMPORARY TABLE ...) are bound to the current database connection, which can be difficult to maintain with PHP - a single web session could use one or many database connections.

I would suggest creating a static table in your database with extra columns, for example:

Temporary student list
web_user_id, matriculation_number, first_name, last_name

This one would allow each user to have a temporary student list, which would persist across database connections and would behave nicely with PHP. The data could exist between user logins if that was useful.

Temporary student list
web_session_id, matriculation_number, first_name, last_name, created_date

This might be more useful if you don't want to attach the list to a particular user. PHP's session handle should be more persistent than a database connection(s), and the created_date would be useful to allow you to do some periodic or automatic cleanups (e.g. get rid of anything older than a day).

Alternatively, you could store nothing in the database. You could create your query entirely within in PHP, and pass the student IDs inline:

SELECT columns FROM STUDENT INNER JOIN EXAM ON STUDENT.ID = EXAM.STUDENT_ID WHERE STUDENT.MATRICULATION_NUMBER IN (123, 234, 345, 456, 567...)

Related Question