Is this an acceptable database design?

My spider sense is tingling, but I've been thinking about it for 2 hours now and I'd like some more feedback from the hivemind.

I'm creating an application for a school. Its supposed to handle students, teachers, courses, honor roles, grades - the works.

I was wondering how to handle the ch开发者_如何学运维ange of years after each year.

• Students move up a grade (or don't).
• Teachers are assigned to different grades as their homeroom teacher.
• Grades are saved for the year.

There's also the matter of auditing. I need to have an easy way to pull up records from last year or the year before. See what teacher gave which course at what grade at what year.

The problem I'm having is how to handle this.

My thought was to create a new clean database for each year as they come along. So at the end of this year, I'd go to the school and create a new database for them named FooSchool2012 and programatically let the end users change the database they want to use via a connection string.

Since I'm using an ORM it's only a matter of changing the connection string as the databases are the same.

But this reeks of bad design and crappy engineering to me.

Usually my gut is right, so hopefully you guys can let me know of some alternatives on how to handle this.

No, I would not create a new table or database for each year. It breaks first normal form. Every table will be a duplicate except for the name. It's a poor design. And a maintenance headache. Who's going to create the new database, load the schema, and then change all the URLs? If you change the schema after a few years, will you have to change all the back editions as well so people can query the historical data?

Nope, not a good design at all.

It's common to move historical information out into reporting/data warehousing databases. But the scheme you're suggesting is reminiscent of old, mainframe, VSAM flat file methodologies. I'd use relational databases the way they were intended to be used.

I'm sure your solution could be made workable, but it does seem a little needlessly complicated. Couldn't you accomplish the same thing in a single database by referencing the school year? You may want to think about which entities make sense to have "effective dates" (i.e., a start and an end time). The 3rd grade teacher may change mid year, for example, but you could handle that with effective dates.

My thought was to create a new clean database for each year

If you thought about this for two hours, and your best idea was to create a new database for each year, you're the wrong person to design this database.

That's an observation, not a criticism. You just need to learn a lot more of the fundamentals before you tackle a project like this one. You'll just get frustrated, and the school will suffer.

You need to spend A LOT of time on your database design. Think about maintenance in the long run, it needs to be as easy as possible. The best way is to create a relational database, research bridge,validation, and base tables. To answer your question, I would not do a table for every year. The best way is having the student grade data mapped to a specific unique id representing that student's specific course ID.

I would think about creating a table for each of the nouns:

instructor - PK instructorID,instructorName.. (any other 1:1 instructor information)

Student - PK studentID,StudentName.. (any other 1:1 student information)

Course - PK CourseID, CourseName, CourseDescription.. (any other 1:1 course information)

•Teachers are assigned to different grades as their homeroom teacher.

on the 1:1 instructor table you could have a column called HomeroomGrade and then you

update that column with the current grade. If you wanted to keep a history of the grade

you could have the instructor table be a composite key with another column incrementing up

for the current record.

•Students move up a grade (or don't).

You will need another table showing the relationship of students to a unique courses

grades for that year, but first you need to map the instructor to that specific course.

PK InstructorToCourseID

InstructorID - FK

CourseID - FK

Year - FK

then yet another table mapping the unique course to that student with the grade..

PK InstructorToCourseID FK from previous table

PK StudentID - FK from student information table

Grade

Sorry if im general and vague, but this should give you some ideas on the relationships that can be created.