0

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.

2
  • How many students are you usually asked about at one time? Also, are you directly writing SQL against the database using something like MySQL workbench, or are you trying to write an application which can be used by other people? Commented Feb 26, 2014 at 14:29
  • @NathanJolly I would say 150 to 200. I am trying to create an application and the language i am using is PHP.
    – Sotiris
    Commented Feb 26, 2014 at 14:38

2 Answers 2

1

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...)

1
  • Thank you all the different solutions are great. If i did not know about temp tables i would have probably used the inline student ids. Still the temp table feels a bit more natural. I will experiment with the temporary table approach first and then if it does not workout i will use the query with the inline ids. Thanks again answer accepted.
    – Sotiris
    Commented Feb 27, 2014 at 9:17
1

I see 2 ways to do this, your temporary table idea or running them through one at a time. Which way to go depends on how many records we are talking about. If its 3 or 4 names at a time then running them individually may be faster but if we are talking larger than 20 or so a temp table would be the way to go.

Overall the best way depends on your requirements. I use temporary tables for things like this all the time, they are quick and easy and definitely not a "Wrong" choice.

My personal choice would be to write a program to have some one else do it but if that is not an option we see where we go.

2
  • i need to check around 150 to 200 students at a time, so doing it manually as you said is counterproductive. I guess i am glad that using temp tables is a solution that someone else also came up with.
    – Sotiris
    Commented Feb 26, 2014 at 14:42
  • 1
    Temp tables are a great way to do these sorts of things. And your right 150 - 200 you will want to use a temp table. Also i have written a couple of programs to do a similar type of thing and i gave the end user the ability to map the columns in the table to the delimited columns in the file (IE: table column 1 is file column 7, table column 2 is file column 2). This way it does not matter which order the columns in the file are, i just pull out what the user told me goes in the table. Commented Feb 26, 2014 at 14:46

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.