Last post Nov 16, 2010 01:43 AM by litlmike
Nov 10, 2010 09:57 PM|litlmike|LINK
I am developing a grading system and trying to figure out the best way to design the database (SQL Server 2008). A teacher will have a test to give to students. Let's say the test has 10 questions, for a total of 100 point test. Let's say that in the
teacher's first year, the 10 questions are all worth 10 points a piece (again, totaling 100). But, let's say in year 2, that the first question is now worth 55 points (55%) and the other 9 questions are 5 points a piece.
Over time, each year, the teacher wants to change the weight (point value) of the questions, but would like to track what day they changed the point values, and would like to tell their application, which set of weights (point values) to use. So, maybe
in year 5, the teacher would like to go back and select the grading system they used in year 1.
So then, how do I setup this database? Specifically, I would think it would go something like this:
If this is how the db should be setup:
1) Is there a way to "tell" the db, that only one row should have their isTheCurrentGradingSystem set to true at a time? Should this be built into the db structure, or some sort of Business Logic Layer?
Database Design One Row True at a time
Nov 10, 2010 11:41 PM|muhammedsalimp|LINK
I don't think there is any DB feature for this.
In my opinion, features like this are best implemented in Business Logic Layer.
After all it is a business logic , Isn't it ?
Nov 11, 2010 09:45 AM|LudovicoVan|LINK
> Over time, each year, the teacher wants to change the weight (point value) of the questions, but would like to track what day they changed the point values, and would like to tell their application, which set of weights (point values) to use.
As a starting point, I think the structure would look like this, where weights are grouped in sets: a teacher would manage these sets, and at any given time would select the set to use.
@muhammedsalimp:> After all it is a business logic , Isn't it ?
And what is *not* business logic Mr Saleem? :) Isn't -say- a *products* table a business entity already?
Nov 11, 2010 05:14 PM|litlmike|LINK
So how would you see the GradingSets acting as? Is it just a table that contains the an id for GradingWeights? And do the questions(1-10) go into gradingSets or GradingWeights? Does that make sense?
Nov 11, 2010 07:32 PM|LudovicoVan|LINK
> So how would you see the GradingSets acting as? Is it just a table that contains the an id for GradingWeights?
The other way round, as the cardinality of the relation implies.
Given the problem statement (and guessing a bit where more details are needed) this is how I would *approach* it. If this is completely unfamiliar, I'd advice reading some tutorial about entity-relationship diagrams (ERDs), the underlying concepts, and how
to "extrapolate" entities and their relations from a given problem statement.
The idea is that a test is related to multiple sets of question gradings, each assigning specific grades to the test's questions. At any given time, which specific grading set is active for any given test is specified in the attribute IsCurrentSet of the
GradingSets table. The rest is proper SQL: to select the currently active grades for the questions of any given test, to join them to a student's answers, to check that a student's answer matches the specified CorrectAnswer for any question, and so on.
Nov 16, 2010 01:43 AM|litlmike|LINK
Thanks, this was very helpful and I will try this method.