redesigning table to accommodate new data types

  • i have a table in which there is  column called Marks of type int. This is used to save the marks scored by students in an exam. Now  besides those students who appreared for the exam and scored some marks,there may be other students who may have been ABSENT or DISALLOWED and i want to store ABSENT and DISALLOWED in the system. Obviously Marks column is not suitable for this since its of type integer. I have done a lot of coding in the front end and i want to bring in the new changes as smoothly as possible. lease suggest ideas. thanks

  • Why not just put the "absent" or whatever in another column? If you try to stuff everything in the same column, querying your data is going to be a nightmare.

  • pietlinden - Sunday, February 19, 2017 10:18 AM

    Why not just put the "absent" or whatever in another column? If you try to stuff everything in the same column, querying your data is going to be a nightmare.

    so another column that accepts only three values(present , absent, disallowed)?
    if the user enter marks for  student in the front end, then automatically enter 'present' for that student in the new column?
    and there will be check boxes /radiobutton for 'absent' and 'disallowed' in the front end? if a user selects 'absent' then enter value 'absent'in thecolumn?
    and the datatype for the column must be varchar . right?

    am  i doing it right?

  • Taking a step backward, what is the table supposed to describe / store data about? Are the values {present, absent, disallowed} mutually exclusive?

  • pietlinden - Sunday, February 19, 2017 11:03 AM

    Taking a step backward, what is the table supposed to describe / store data about? Are the values {present, absent, disallowed} mutually exclusive?

    okay this table is meant to store the marks scored by students in exams. therefore it contains student_id , and marks and an identity column  among others. 
    yes they {present, absent, disallowed} are mutually exclusive. a student can be either present or absent or disallowed in an exam.

  • Something like this?

    CREATE TABLE dbo.Grades (
        StudentID INT
        ,ExamID INT
        ,Grade TINYINT
        ,StudentStatus VARCHAR(10)
    CONSTRAINT pkGrades PRIMARY KEY (StudentID, ExamID)
    );
    GO

    ALTER TABLE dbo.Grades ADD CONSTRAINT CK_StudentStatus
    CHECK (StudentStatus IN ('Present', 'Absent', 'Disallowed'));

    INSERT INTO dbo.Grades VALUES (1,1,95,'Present');
    INSERT INTO dbo.Grades VALUES (1,2,NULL,'Disallowed');
    INSERT INTO dbo.Grades VALUES (1,3,NULL,'Missing');

    The first two inserts will succeed. The third will fail, because it violates the check constraint.
    If you use a dropdown/combobox in the front end, you can force the choices to be limited to a list, and then your problem is a non-issue, because it's handled in the front-end and not the database.

  • I was thinking of using a text box for marks , one radio button for Present and another for Disallowed. And then to save the marks in the table , either the text box must have some value, or Present  radio button must be selected or Disallowed radio button must be selected. Is it alright ?

  • Does it work?  Can you query the table to get the answers you need?

  • 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).

    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".

  • I'm wondering how you would represent that a teacher decides that an absent student without a viable excuse for that absence gets an assigned score of 0 for that exam, and that the 0 needs to count towards their grade.   You need to be able to represent that possibility, even if that's not current policy.   Policies can change, and it's harder to change a database than a policy.   If you have an absent student, you might have a NULL value for a grade, but with such a policy, need to have it evaluate to a zero.   It's usually better to represent the data as it is intended to be computed than requiring additional business logic, but there are some alternatives.   Just trying to keep the focus on what all the possible situations are that you might need to be able to represent in the database.    Also, while it may not be ideal, a negative integer value would be unrealistic as representing an actual score on a test, and could, under the right circumstances, represent specific conditions...  Of course, that may mean a number of adjustments to other queries to avoid having such data appear where it doesn't belong, and while not something I'd recommend, it could avoid the additional field, but at a cost, should "powers that be" interfere with rational decision making...

  • If the Marks column is intended to be numerically additive, for example summed or averaged for a cumulative score, then you don't want special codes representing absent or other otherwise. You could have the column be nullable, in which case NULL would mean not graded and excluded from the average. Perhaps at some point if the student hasn't taken a makeup exam, then that NULL would be updated to a 0 which would then effect the average. But that's just how you tabulate the total or average Mark. You also need a status column indicating more detail about whether the exam should be retaken, etc.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Don't model the code or the logic, model the data.  Do logical modeling first, then convert that to a physical model.  Don't just start out with a physical model.

    Sorry, I had to say that, even though I know there's no realistic chance you'll really do that, even though it would give you a vastly better design and save you orders of magnitude of time later in correcting/adjusting table designs and bad data caused by poor design.

    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".

  • k sorry for the delay everyone. I hope i can still get some precious inputs from you all.
    k i have never used constraints in my database before, therefore this is what i am thinking. tell me if it is k.

    I will create a table , say ExamStatus with two columns 'StatusId' of Integer datatype and  'Status' of type varchar.
    StatusId ...........Status
    1   ...............  Present
    2   ..................Absent
    3   ...............   Disallowed

    I will  add a column, StatusId  (which will be a foreign key from ExamStatus table) to the first table StudentsMarks .

    On my front end, the user has to either enter Marks obtained or select Disallowed button or select Absent button. so there is some kind of constraint in the sense the StatusID column in StudentMarks will never be empty. If the user select Absent or Disallowed then the front end will automatically enter 0 to Marks column. i know this approach i very basic but is this ok?

  • I think the approach i have just stated is not proper actually. Suppose the user wants to see  all the marks of a particular student.  If the student was Absent or Disallowed , then the Marks column must not show the value 0 (zero) .There may be some students who will be Present and still get 0 marks. So there must be some kind of distinction here. Keeping this in mind, should i leave the Marks column as Null if the student is Absent or Disallowed? and if so, how can i add integer will Null values if i need to find the total marks of a student?

  • can anyone come in here?

Viewing 15 posts - 1 through 15 (of 32 total)

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