How do I keep one column consistently updated?

Question

Hello, another newbie mysql question

I have a faculty table, and each faculty has a certain number of students under him/her. So there is a 'current' column, which is the number of students currently under them.

However, I don't want to ++ and -- everytime I switch a student to another faculty. Is there a way to keep the column updated with a query that uses count()? I find it is easier and more accurate to use the query 'select count() from student where advisor = 2' for example that using my current column.

Answer 1

To do this, use a view:

CREATE VIEW studentCount AS 
SELECT 
    profID, 
    profName, 
    whatever, 
    (SELECT COUNT(*) 
     FROM studentTable 
     WHERE studentTable.profID=profTable.profID
    ) AS studentCount 
FROM profTable;

Obviously, this needs to be massaged a little to fit your schema, but essentially, setup your view to have all the columns of the table with the faculty info and add a column at the end that counts the number you want in it.

Answer 2

Triggers could be a solution to you problem?

http://dev.mysql.com/doc/refman/5.5/en/triggers.html

You could create a trigger that automaticly updates your faculty table every time a student switch faculty.