Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am developing a school management system.

Most data will not be reused after the end of the school year.

I am contemplating using schemas to separate data in various school years, however I am afraid I don't know very much about schemas.

Would it be a good idea to use a new schema for each school year to store data (see below), rather than adding a year field and using a where clause for all queries?

schema_year1.attendance_of_staff
schema_year2.attendance_of_staff
share|improve this question
1  
If you use a new schema for each school year, you will be faced with needing to recode your queries each time a new schema is created. – Max Vernon Dec 11 '13 at 20:59
    
Cant we do it programmatic? @MaxVernon Thanks – Abel Jojo Dec 12 '13 at 17:01
    
"Most data will not be reused after the end of the school year", uh huh – Neil McGuigan Jan 10 '14 at 19:08

Maybe, you should use staff, attendance_staff, and school.

staff table
==========
id, 
fullname,
birthday
...

school Table
==============
id,
name, 
founded,
number_of_staff
...

attendance_staff table
=======================
id,
staff_id,
school_id,
attended_date,
...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.