redesigning table to accommodate new data types

Question

redesigning table to accommodate new data types

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

hlsc1983 SSCommitted Points: 1971 More actions · Answer 1

ScottPletcher - Friday, April 7, 2017 2:29 PM
hlsc1983 - Friday, April 7, 2017 2:13 PM
ScottPletcher - Thursday, April 6, 2017 1:17 PM
hlsc1983 - Thursday, April 6, 2017 12:44 PM
ScottPletcher - Monday, February 20, 2017 10:26 AM
You don't want to store a string, use a numeric code instead; strings would be a huge waste of resources and are harder to change Also, I'd separate the present/not present from the reasons for not present to properly model the data and simplify changes later.
I'd suggest something like below. After you fill in these values for current rows, you may want to change the columns to "NOT NULL" and adjust the check constraints accordingly.
ALTER TABLE <table_name> ADD was_present bit NULL, reason_not_present tinyint NULL /*valid values are: 0=was present; 1=ABSENT; 2=DISALLOWED*/ CONSTRAINT <table_name__CK_reason_not_present> CHECK(reason_not_present IS NULL OR reason_not_present <= 2); ALTER TABLE <table_name> ADD CONSTRAINT <table_name__CK_present_and_reason_not_present CHECK( (was_present = 1 AND reason_not_present IS NULL) OR (was_present = 0 AND reason_not_present IS NOT NULL) OR /*optional: you may not want to force a "reason_not_present" to be given*/ (was_present IS NULL AND reason_not_present IS NULL) ) /*this can be removed once you adjust current data to not have NULL in present & reason*/
You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint). You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint).
thanks for the input. one doubt i have is this. If there is 'was_present' bit, why is there a need for the value zero to represent 'was_present' for the 'reason_not_null' column? is this not redundant data?
It's just to make it more self-evident / clearer, and it only costs 1 bit. Sure, you could just use "reason_not_present", but then you'd need to explicitly tell new developers that "0" in that column means the student really was present. That's counter-intuitive, that the reason for not being present represents actually being present! But I would expect any developer, even a new one, to be able to understand a bit column of "was_present", and a separate "reason_not_present" code, without any explanation at all.
k i have created a table:
create table ExamStatus( Status_id tinyint primary key, Status_name varchar(15) not null, );
Inserted three rows:
insert into ExamStatus(Status_id,Status_name) values (0,'Present'),(1,'AB'),(2,'DC')
And added a foreign key:
alter table Students_Subjects_junction add constraint FK_inSSJ_fromExamStatus foreign key (reason_not_present) references ExamStatus(status_id)
i need the values AB (meaning absent) and DC( meaning discollegiate/disallowed) since these strings have to be printed out in the marksheet if the student is absent or disallowed.if a student appears for the exam, then the grade scored will be printed in the marksheet.
Now there arises another query. When i print the marksheet the latest marks for a particular subject must be printed out in the marksheet. There is no problem if a student sits for a particular course say Physics only once and clears it. But if a student fails in this paper or is Absent or Disallowed, then in this case there will be more than one row for a student - course pair. Example. In the first attempt, student fails with score 8 out of 100. So he appears again with a score of 50. In this case , there is a row each for two attempts. Another example is a student may be DC candidate, and then he may score 30 next time. Here too,there is a row each for two attempts.
Keeping these possibilities, i need to extract the latest row for a student-course pair in the Students_Subjects_junction table . How can i get latest values for generating the marksheet? do i need to introduce another column for storing the date of entry?
If it's taken as part of a class / course, the term (such as Fall 2017) should (must) be stored as part of the class, yes. so either the subject_id and term (preferably) or the date (if you couldn't use term) would be stored.

Before i move on to the date of entry, there is something very important. K so now the ExamStatus table is done . Using the result of this query i can generate the marksheets in Crystal reports. I use crosstab in Crystal reports for the marksheet.

SELECT S2student_rollno S2..student_rollno,, SSJmarks SSJ..marks,, Ssubject_name Ssubject_id S..subject_name,, S..subject_id,, GTGrade_id GT..Grade_id,, TPThPr_name TP..ThPr_name,, EPETPA_name EP..ETPA_name,, SGTJmaximum_marks SGTJpass_marks SGTJ..maximum_marks,, SGTJ..pass_marks FROMFROM Students_Subjects_junction SSJ Students_Subjects_junction SSJ Subjects S SSJsubject_id Ssubject_id JOINJOIN Subjects S ONON SSJ..subject_id == S..subject_id Students S2 SSJstudent_pk S2student_pk JOINJOIN Students S2 ONON SSJ..student_pk == S2..student_pk Gradetype GT SSJgrade_id GTgrade_id JOINJOIN Gradetype GT ONON SSJ..grade_id == GT..grade_id TheoryPractical TP TPThPr_id GTThPr_id JOINJOIN TheoryPractical TP ONON TP..ThPr_id == GT..ThPr_id EndtermProgressive EP EPETPA_id GTETPA_id JOINJOIN EndtermProgressive EP ONON EP..ETPA_id == GT..ETPA_id Subjects_Gradetype_junction SGTJ Ssubject_id SGTJsubject_id GTGrade_id SGTJGrade_id JOINJOIN Subjects_Gradetype_junction SGTJ ONON S..subject_id == SGTJ..subject_id ANDAND GT..Grade_id == SGTJ..Grade_id WHEREWHERE S2semester_id 7 S2department_id 14 SSJ[year] 2017 S2..semester_id == 7 ANDAND S2..department_id == 14 ANDAND SSJ..[year] ==2017;; c

This is fine as long as the there is a value in the marks coulmn. But lets say the student is Disallowed or absent , then the marks coulmn has a NULL value. But in the marksheet I dont want to show blank space(NULL value) .Rather i wan to show â€˜DCâ€™ or â€˜Absentâ€™ wherever applicable.

This is the format of the marksheet that i need.

As you can see, marks value for course G102 (Progressive Assessment)is actually a string Absent, and DC for G-104(End term Theory and End term Progressive Assessment)

The logic is pretty simple i guess. If marks column is NULL, then fetch the value of Status_name from ExamStatus table using the foreign key â€˜was_presentâ€™ in Students_Subjects_junction.But how do i get it to work?

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

ScottPletcher - Monday, February 20, 2017 10:26 AM
You don't want to store a string, use a numeric code instead; strings would be a huge waste of resources and are harder to change Also, I'd separate the present/not present from the reasons for not present to properly model the data and simplify changes later.
I'd suggest something like below. After you fill in these values for current rows, you may want to change the columns to "NOT NULL" and adjust the check constraints accordingly.
ALTER TABLE <table_name> ADD was_present bit NULL, reason_not_present tinyint NULL /*valid values are: 0=was present; 1=ABSENT; 2=DISALLOWED*/ CONSTRAINT <table_name__CK_reason_not_present> CHECK(reason_not_present IS NULL OR reason_not_present <= 2); ALTER TABLE <table_name> ADD CONSTRAINT <table_name__CK_present_and_reason_not_present CHECK( (was_present = 1 AND reason_not_present IS NULL) OR (was_present = 0 AND reason_not_present IS NOT NULL) OR /*optional: you may not want to force a "reason_not_present" to be given*/ (was_present IS NULL AND reason_not_present IS NULL) ) /*this can be removed once you adjust current data to not have NULL in present & reason*/
You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint). You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint).

is there anything wrong with your query? should it not be like this ?
CHECK( if was_present =1(true) and reason_not_present <=2) ?
the reason being that if was_present is true then reason_not_present must be 0 right . it cannot be null in this case.

ScottPletcher SSC Guru Points: 101118 More actions · Answer 3

hlsc1983 - Sunday, April 9, 2017 9:55 AM
ScottPletcher - Monday, February 20, 2017 10:26 AM
You don't want to store a string, use a numeric code instead; strings would be a huge waste of resources and are harder to change Also, I'd separate the present/not present from the reasons for not present to properly model the data and simplify changes later.
I'd suggest something like below. After you fill in these values for current rows, you may want to change the columns to "NOT NULL" and adjust the check constraints accordingly.
ALTER TABLE <table_name> ADD was_present bit NULL, reason_not_present tinyint NULL /*valid values are: 0=was present; 1=ABSENT; 2=DISALLOWED*/ CONSTRAINT <table_name__CK_reason_not_present> CHECK(reason_not_present IS NULL OR reason_not_present <= 2); ALTER TABLE <table_name> ADD CONSTRAINT <table_name__CK_present_and_reason_not_present CHECK( (was_present = 1 AND reason_not_present IS NULL) OR (was_present = 0 AND reason_not_present IS NOT NULL) OR /*optional: you may not want to force a "reason_not_present" to be given*/ (was_present IS NULL AND reason_not_present IS NULL) ) /*this can be removed once you adjust current data to not have NULL in present & reason*/
You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint). You can also add a FK and a table for the "reason_not_present" values, or translate them in a view (a FK relationship is better from a design standpoint).
is there anything wrong with your query? should it not be like this ?
CHECK( if was_present =1(true) and reason_not_present <=2) ?
the reason being that if was_present is true then reason_not_present must be 0 right . it cannot be null in this case.

That's true, you should adjust the CHECK conditions depending on the specific data rules you are able to put in place. For example, using 0s and avoiding NULLs (a very good idea if you can do it, since NULLs can be very confusing), you could do this:

CHECK( (was_present = 1 AND reason_not_present = 0) OR (was_present = 0 AND reason_not_present > 0) )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".