I am trying to come up wit a database design for a small school. it works this way: in AC-Year, there are 3 terms with each term having 2 semesters. New students get registered at the start of each term. A student may take entrance exam. if he fails, he starts at freshman then after the semester, he can select department and program (i.e. diploma, certificate...). A student can select certificate accounting, for e.g., but if he is good, he can continue to diploma accounting program, taking the courses he needs only.
how do i store that information? How do i keep track of where he is studying? If he is upgraded, how do i keep his past? Please some tip as i am totally lost on this one.