Creating a database table from an entity with a list

Question

I'm creating an application that has Professors create Tests. Each Test is composed of n amount of Questions that the Professor creates.

I have a Question Entity:

public class Question {
    private int questionID;
    private int testID;
    private String question;
    private String answer;
    private int points;

    public int getQuestionID() {
        return questionID;
    }

    public void setQuestionID(int questionID) {
        this.questionID = questionID;
    }

    public int getTestID() {
        return testID;
    }

    public void setTestID(int testID) {
        this.testID = testID;
    }

    public String getQuestion() {
        return question;
    }

    public void setQuestion(String question) {
        this.question = question;
    }

    public String getAnswer() {
        return answer;
    }

    public void setAnswer(String answer) {
        this.answer = answer;
    }

    public int getPoints() {
        return points;
    }

    public void setPoints(int points) {
        this.points = points;
    }
}

And I have a Test Entity:

import Question;

public class Test {
    private int testID;
    private int courseID;
    private String testName;
    private List<Question> questions;

    public int getTestID() {
        return testID;
    }

    public void setTestID(int testID) {
        this.testID = testID;
    }

    public int getCourseID() {
        return courseID;
    }

    public void setCourseID(int courseID) {
        this.courseID = courseID;
    }

    public String getTestName() {
        return testName;
    }

    public void setTestName(String testName) {
        this.testName = testName;
    }

    public List<Question> getQuestions() {
        return questions;
    }

    public void setQuestions(List<String> questions) {
        this.questions = questions;
    }

}

The Test Entity has the list of Questions, and I'm not sure how to make that into a table. I would prefer to avoid a multi-value field in the table for a Test.

One idea that may be a possible solution is to create a table in the database that would help map the questions. I could just call it TestQuestion and it would have TestID(int):QuestionID(int) as its columns. The Test table would have columns (testID, courseID, testName). Then when I do the querying for a Test, I also query the TestQuestion Table for the list of questions and put those in the list in the Test Entity.

I would like to do this all with good practice.

Your approach of having a table map Test and Question looks fine to me, assuming that these two entities have a many-to-many relationship which seems to be the case here. — Invisible Arrow
– Invisible Arrow, Commented Dec 10, 2014 at 16:46
If you create a TestQuestion row by using the id fields, someone can change a Question row text in the future, and the wrong (new) question will be linked. If this is true, you either have to prevent questions from being changed, or you have to create a UsedQuestion table. — Gilbert Le Blanc
– Gilbert Le Blanc, Commented Dec 10, 2014 at 16:46

Panickos Neophytou · Accepted Answer · 2014-12-10 19:10:43Z

1

Your class definitions show that each Question instance belongs to a single Test instance, since you have a single testId in the Question class, and not a list of testIds. On the other hand a single Test may have multiple Questions. So the relation between Test and Question is one-to-many.

In order to implement these entities into a Database schema you only need 2 tables, which are joined by having the test id as a foreign key into the Question table:

Test(id, course_id, test_name, ...)
Question(id, test_id, question, answer, ...)

Query to get all the questions belonging to a test:

SELECT *
FROM Question
WHERE test_id = 1

Query to get all the tests with their corresponding questions:

SELECT *
FROM Question, Test
WHERE Question.test_id = Test.id
GROUP BY Question.test_id

answered Dec 10, 2014 at 19:10

Panickos Neophytou

5255 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eric Over a year ago

Would my Entity class (as shown above) stay the same? I want the Test to have a list of questions in it. That would mean my entity class is a bit different then the class table. Is that okay?

Eric Over a year ago

I went ahead a tried this and I like it thus far, however, I'm a bit confused on grabbing the task from the database. Right now I have to do two queries. One query gets all the activities and puts them into a List. The other query gets the actual task and then puts the List<Activity> into the task. Maybe there is a better way to do this?

Panickos Neophytou Over a year ago

If by Task you mean Test and by Activity you mean Question, then that should be fine. I don't have any other information on your application, but if you are instantiating 1 Test at a time, then doing the 2 queries (one for the test and one for its questions) is totally fine.

Collectives™ on Stack Overflow

Creating a database table from an entity with a list

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related