Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2012
    Posts
    9

    Simple question about table design

    Hi, just start learning ERD, sorry if this is too noobish.
    Say I want to store the different exam papers results a person has in a database, since the papers have different number of attributes, they will have their own table instead of a common table with a column of different exam types. So I have at least a Person table, and Exam_A, Exam_B, Exam_C tables.
    A Person can take multiple of Exam_A, Exam_B, and Exam_C, but each exam is only assigned to one person.
    Usually I will have to answer questions like "Show all the exams taken by John" rather than questions like "Show all the persons who have taken exam A".
    How would you design the tables?
    1. Each Exam_A/B/C table store a FK referencing the Person?
    or 2. An extra table PersonExam storing both FKs from Person and Exam_A/B/C?

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'd have a supertype exam table, such that exam_a, exam_b, etc., are subtype tables

    then link the supertype table to the person table

    do a search for supertype/subtype for more info
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by NewHere View Post
    I want to store the different exam papers results a person has in a database, since the papers have different number of attributes, they will have their own table instead of a common table with a column of different exam types.
    Most likely, the differences between the exams is their content, not their design. In the simplest form, each of the three exams will have a series of questions and the appropriate answer. So the exams can be stored in a single table with these fields QuestionID, ExamType, Question, Answer. You really want to avoid the design with Exam_A, Exam_B, Exam_C tables. If you have to store different fields, not just different values, then use a supertype table as suggested before.

    Since a person can take the exam multiple times you will need a join table to keep the history of their results. But this does not make any indication of what exam type a person is assigned to. You could add ExamType to the Person table and also maintain a PersonExam table that would link the person to the results of their exam. The design would depend on if you are storing a question by question result, the final score, or simply a pass/fail

    From the little bit I know of the requirements I am inclined to make 3 tables: (this is assuming a question by question result storage where you don't care what their answer was, only if they got it correct)

    ExamQuestions (QuestionID, ExamType, Question, Answer)
    PersonExam (PersonId, QuestionID, isCorrect, ExamDate)
    Person (PersonId, ExamType) - and whatever other info you need

    Steve

  4. #4
    Join Date
    Jul 2012
    Posts
    9
    Hi, thank you for both of your advices, but I dont quite get the idea of using one exam table to store the data.

    Say these are the data I would like to store: (to make things easier, each exam paper can only be taken by each person in each year)
    PersonName, ExamYear, ExamPaper, PushUp, SitUp, Running, Typing, EyeSight...
    So the raw data are (e.g.)
    Name: John, Year: 2011, Paper: Fitness
    Results: PushUp(30), SitUp(60), Running(12min)
    Name: John, Year: 2012, Paper: Fitness
    Results: PushUp(35), SitUp(65), Running(10min)
    Name: John, Year: 2011, Paper: Office
    Results: Typing(100wpm),...
    Name: John, Year: 2011, Paper: Field
    Results: EyeSight(6m), Running(2km),...
    Name: Tom, Year: 2011, Paper: Fitness
    Results: PushUp(30), SitUp(60), Running(9min)
    Name: Tom, Year: 2011, Paper: Field
    Results: EyeSight(10m), Running(4km),...

    Like I said, every exam paper has different number of fields and attributes and units, why grouping them into same table is suggested by both of you?
    Is there a big advantage on that?
    Thank you

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by NewHere View Post
    Like I said, every exam paper has different number of fields and attributes and units, why grouping them into same table is suggested by both of you?
    not by me

    i suggested supertype/subtype tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by NewHere View Post
    Like I said, every exam paper has different number of fields and attributes and units, why grouping them into same table is suggested by both of you?
    and I only suggested it if the fields were the same...

    Quote Originally Posted by NewHere View Post
    So the raw data are (e.g.)
    Name: John, Year: 2011, Paper: Fitness
    Results: PushUp(30), SitUp(60), Running(12min)
    Name: John, Year: 2012, Paper: Fitness
    Results: PushUp(35), SitUp(65), Running(10min)
    Name: John, Year: 2011, Paper: Office
    Results: Typing(100wpm),...
    Name: John, Year: 2011, Paper: Field
    Results: EyeSight(6m), Running(2km),...
    Name: Tom, Year: 2011, Paper: Fitness
    Results: PushUp(30), SitUp(60), Running(9min)
    Name: Tom, Year: 2011, Paper: Field
    Results: EyeSight(10m), Running(4km),...
    You are essentially storing Name, Year, Paper and a string of Results that vary based on the Paper. Your original question was how would I design these tables, and given what you have here I would keep it as one:

    The Exam table can have two fields, Paper and Test (and an ID)
    so the data would be something like:

    ExamID, Paper Test
    1, Fitness, PushUp
    2, Fitness, SitUp
    3, Field, EyeSight
    4, Field, Running
    5, Office, Typing

    or for each person you only need to store the Tests that are appropriate to their paper and year so:

    Name, Year, Test, Result
    Tom, 2011, PushUp, 30
    Tom, 2011, SitUp, 60
    Tom, 2011, Running, 9min

    and, if desired, you could store the paper type in the person table. Of course, the join tables are a lot easier (and safer) if they use ID's rather than names. I just kept it as words here for clarity

    Steve

  7. #7
    Join Date
    Jul 2012
    Posts
    9
    Quote Originally Posted by r937 View Post
    not by me

    i suggested supertype/subtype tables
    Sorry, I must have read some bad supertype/subtype examples online.
    Cause I was given the idea that supertype/subtype is a tree structure stored within the same table with self-referencing ID.

    Quote Originally Posted by sps View Post
    and I only suggested it if the fields were the same...
    The Exam table can have two fields, Paper and Test (and an ID)
    so the data would be something like:

    ExamID, Paper Test
    1, Fitness, PushUp
    2, Fitness, SitUp
    3, Field, EyeSight
    4, Field, Running
    5, Office, Typing

    or for each person you only need to store the Tests that are appropriate to their paper and year so:

    Name, Year, Test, Result
    Tom, 2011, PushUp, 30
    Tom, 2011, SitUp, 60
    Tom, 2011, Running, 9min

    and, if desired, you could store the paper type in the person table. Of course, the join tables are a lot easier (and safer) if they use ID's rather than names. I just kept it as words here for clarity
    If I store also the paper type in the person table, like
    Name, Year, Paper, Test, Result
    Tom, 2011, Fitness, PushUp, 30
    Tom, 2011, Fitness, SitUp, 60
    Tom, 2011, Field, Running, 9min
    then I only need to maintain this table, because I dont really need the exam table anymore, right?
    With this single table, I can quickly answer queries like:
    List all the exam results taken by Tom
    List all the Fitness exam results in 2011
    but the only downside is a big table, am I right?

    If I don't store the paper type in the person table, then I will loss the linkage between exam paper and the person?

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by NewHere
    then I only need to maintain this table, because I dont really need the exam table anymore, right?
    With this single table, I can quickly answer queries like:
    List all the exam results taken by Tom
    List all the Fitness exam results in 2011
    but the only downside is a big table, am I right?
    There are many downsides of doing this and you really shouldn't consider it!
    I'm sorry if it came across this way but you shouldn't be storing the results of the tests in the person table. Those should be in a join table, PersonExam, or something similar. You want to avoid duplicated data whenever possible. What happens if EyeExam is changed to VisionExam in the future? You would have to update every instance in your table rather than a single spot.

    The person table should only have one record per person with the items that identify that person. Since a person can only take one kind of test, the paper type can be stored with the person. That allows you to link with the Exam table and get all of the appropriate tests for that person.

    You should have, at a minimum, three tables: Person, Exam, and PersonExam. Where PersonExam stores the FK id from both Person and Exam along with the year and results. Since running is part of both the Field test and the Fitness test you want to make sure you always use the Exam ID rather than the Exam name. How I wrote it in the last post was just for clarity, the PersonExam table really looks something like this:

    PersonID, Year, ExamID, Result
    3, 2011, 3, 30
    3, 2011, 1, 12
    1, 2012, 5, 60
    3, 2012, 3, 33
    3, 2012, 1, 11

    Once joined with the Person and Exam tables you get back to the human friendly version.

    Steve

  9. #9
    Join Date
    Jul 2012
    Posts
    9
    Quote Originally Posted by sps View Post
    There are many downsides of doing this and you really shouldn't consider it!
    I'm sorry if it came across this way but you shouldn't be storing the results of the tests in the person table. the PersonExam table really looks something like this:

    PersonID, Year, ExamID, Result
    3, 2011, 3, 30
    3, 2011, 1, 12
    1, 2012, 5, 60
    3, 2012, 3, 33
    3, 2012, 1, 11
    Thank you sps. Just one more final question before I am fully understand.
    Back to my first post, what if I have only one Person and one Exam table (per each exam)?
    So Person is personal details plus a personID
    An Exam table is personID plus the exam details
    Like:
    Person : {PersonID, somedetails...}
    Fitness_Exam : {PersonID, Year, PushUp, SitUp, Running}
    Field_Exam : {PersonID, Year, EyeSight, Running}
    Office_Exam : {PersonID, Year, Typing}

    Now I can quickly answer queries like 'List all the fitness test results of John' by joining Person with Fitness_Exam only (instead of joining three tables).

    In other words, I move the logic to the input (by selecting the appropriate table to join), while your three tables method leaves the logic in the SQL query (by having WHERE Exam.Paper = 'Fitness')

    Are there any pit falls if I do this?
    Thank you!

  10. #10
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by NewHere View Post
    Person : {PersonID, somedetails...}
    Fitness_Exam : {PersonID, Year, PushUp, SitUp, Running}
    Field_Exam : {PersonID, Year, EyeSight, Running}
    Office_Exam : {PersonID, Year, Typing}

    Now I can quickly answer queries like 'List all the fitness test results of John' by joining Person with Fitness_Exam only (instead of joining three tables).
    One major issue I see with this is you need to know what kind of exam a person has before creating a query! You would end up maintaining three queries which is an unnecessary duplication. It leaves you open to a scenario where one of the three queries operates differently than the others. Also, what happens if John switches rolls and next year is taking the field test?

    A second pitfall of this design is it adds more complexity if anything changes in the future. You are naming the fields of the results based on the tests. If a new test component was added to one of the exams you would have to update the tables, forms and the related query. It also leaves the possibility of having null values in the table if a person only completes two of the three exams. (and many nulls if a new exam was added)

    Quote Originally Posted by NewHere View Post
    In other words, I move the logic to the input (by selecting the appropriate table to join), while your three tables method leaves the logic in the SQL query (by having WHERE Exam.Paper = 'Fitness')
    It actually moves things to the database where the table relationships provide the necessary information. To answer the question you posed: 'List all the fitness test results of John' you would do something like (assuming John is personID 7):

    Code:
    select Person.Name, PersonExam.Year, PersonExam.ExamID, PersonExam.Result 
    from Person join PersonExam on Person.ID = PersonExam.PersonID
    where person.ID = 7
    The exam type is irrelevant at this point, but if you need to know it you just pull in the exam table and you have it through the ExamID.

    Your design makes questions like 'Show everyone who took a exam in 2011' or 'List all the Fitness exam results in 2011' very difficult to answer. They would require joining 4 tables where by combining the exams into one table they can be done by doing something like this:
    Code:
    select PersonExam.* 
    from PersonExam join Exam on PersonExam.ExamID = Exam.ExamID 
    and Exam.Paper = 'Fitness'
    so my question is, why do you want to split the exams into three tables? Is it a fear of the queries?

    Steve

  11. #11
    Join Date
    Jul 2012
    Posts
    9
    Thank you very much Steve, your explainations are very clear and helpful.
    I think I am still new to SQL and am deeply influenced by OO design, which makes me tend to separate stuff into individual tables.
    I will try to think what the DB gurus think and learn from you guys
    Cheers

  12. #12
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    You're welcome, I'm glad to help.

    However, I don't want to leave you with the thought that you shouldn't split tables. Keeping one big table is definitely not a good design! Just as in OO, it's more about pulling the common attributes together. The goal is to avoid duplication of data, provide uniquely identified records, and maintain relationships between the items.

    In this case, your exam types share a common pattern and are suitable to be kept together. If you had an attribute that was used by only one exam then you wouldn't want that in the table with the other two.

    It's the same concept you would use if creating a database of employees. You would have a common employee table that kept all the common attributes, you wouldn't design that database with separate employee tables for every type of employee (hourly, salaried, manager, shift, corporate, store, etc). If a particular group of people needed additional info you would connect a second table for that but every employee would still be in the main table.

    Do some reading on database normalization, that will give you a better understanding than anything I could write!

    Good luck with the project.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •