November 10, 2016 at 10:28 am
hi,
i would love some advice on how to best model this problem:
i need to attribute grades to students, grades can be of different types, ex: interval 0-20, interval from 1-10, list/interval of values [bad, good, not evaluated, excellent]
using this stating approach
create table Grades
(
grade_id int not null identity(1,1),
grade_type_id int not null,
grade_value nvarchar(10) not null,
)
-- (1,1,0) ... (20,1, 20)
-- (21,2,1) ... (31,2, 10)
-- (32,3, 'bad')
create table GradeTypes
(
grade_type_id int not null identity(1,1),
grade_type_name nvarchar(100) not null
)
-- (1, '0-20')
-- (2, '1-10')
-- (3, 'Quality')
create table StudentGrades
(
stu_grade_id int not null identity(1,1),
grade_id int not null,
student_id int not null,
)
--(1, 20, STUDENT_ID1)
--(2, 10, STUDENT_ID1)
--(3, 32, STUDENT_ID2)
is this acceptable ? can be improved ?
thank you in advance.
November 10, 2016 at 10:39 am
Is this acceptable? No.
Can this be improved? Yes.
Most importantly, you do not need an identity column on two of these tables. Despite what some people may imply, there's no eleventh commandment that "all tables must have an identity column, and that identity column should be the clustering key". That's pure myth, and a very highly destructive one.
That said, I can't tell from such limited what the best clustering key would be, but I've guessed as best I can.
Secondarily, I'm almost certain the grade_value could be just varchar rather than nvarchar.
Can't do any further modeling without more details. For example, there's no indication of term / semester, so it's not possible to store grades for multiple time periods. At any rate, this seems like a homework q, so more details from me at this point is likely not advisable anyway.
CREATE TABLE dbo.Grades
(
grade_type_id int not null,
grade_value varchar(10) not null,
CONSTRAINT Grades__PK PRIMARY KEY CLUSTERED ( grade_type_id, grade_value ) WITH ( FILLFACTOR = 99 )
);
CREATE TABLE dbo.GradeTypes
(
grade_type_id int not null identity(1,1),
grade_type_name varchar(100) not null,
CONSTRAINT GradeTypes__PK PRIMARY KEY CLUSTERED ( grade_type_id ) WITH ( FILLFACTOR = 100 )
);
CREATE TABLE dbo.StudentGrades
(
student_id int not null,
grade_id int not null,
CONSTRAINT StudentGrades__PK PRIMARY KEY CLUSTERED ( student_id, grade_id ) WITH ( FILLFACTOR = 98 )
);
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 10, 2016 at 12:55 pm
Thank you for the input.
A term or semester is not really important or needed right now, since each grade will be associated also with a curricular unit/discipline and also i am not keeping previous grades of exams ex, just the final grade (even if i think people will like to know all the intercalary exam grades).
In the beginning, we were just storing grades in the scale 0-20, then other courses opened and there was the need to keep track of grading like ex,
Pass with no corrections
Pass with minor corrections
Pass with major corrections
Fail
PS: how important is or why should we specify the WITH ( FILLFACTOR = 98 ) for the index's ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply