A query on Hibernate database design

Question

I am writing a simple web application where I have a User entity and set of pre-defined questions to be answered by the User. The answers provided by the users for these questions would need to be stored against the userId and questionId. After a bit of googling and with the help of stackoverflow, I have designed the tables in the following way.

UserDetails(userId, userName)
QuestionMaster(questionId, question)
QuestionAnswer(userId, questionId, answer, date) PK(userId, questionId)

The Hibernate Entity classes:

UserDetails.class

@Entity
@XmlAccessorType(XmlAccessType.NONE)
@XmlRootElement(name = "user")
public class UserDetails {

private int userId;
private String userName;
private Collection<QuestionAnswer> listOfAnswers = new HashSet<QuestionAnswer>();

@XmlElement (name = "answer", type=QuestionAnswer.class)
@OneToMany(fetch = FetchType.EAGER, mappedBy = "pk.userDetails", cascade=CascadeType.ALL)
public Collection<QuestionAnswer> getListOfAnswers() {
    return listOfAnswers;
}

public void setListOfAnswers(Collection<QuestionAnswer> listOfAnswers) {
    this.listOfAnswers = listOfAnswers;
}

@XmlElement (name = "userId")
@Id @GeneratedValue(strategy=GenerationType.AUTO)
public int getUserId() {
    return userId;
}

public void setUserId(int userId) {
    this.userId = userId;
}

@XmlElement (name = "userName")
@Column(nullable=false)
public String getUserName() {
    return userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}
}

QuestionMaster.class

@Entity
@XmlAccessorType(XmlAccessType.NONE)
@XmlRootElement(name="question")
public class QuestionMaster {

private int questionId;
private String question;
private Collection<QuestionAnswer> listOfAnswers = new HashSet<QuestionAnswer>();

@OneToMany(fetch = FetchType.EAGER, mappedBy = "pk.questionMaster", cascade=CascadeType.ALL)
public Collection<QuestionAnswer> getListOfAnswers() {
    return listOfAnswers;
}

public void setListOfAnswers(Collection<QuestionAnswer> listOfAnswers) {
    this.listOfAnswers = listOfAnswers;
}

@XmlElement(name="quesId")
@Id @GeneratedValue(strategy=GenerationType.AUTO)
public int getQuestionId() {
    return questionId;
}

public void setQuestionId(int questionId) {
    this.questionId = questionId;
}

@XmlElement(name="ques")
@Column(unique=true, nullable=false)
public String getQuestion() {
    return question;
}

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

QuestionAnswer.class (fixed the typo)

@Entity
@AssociationOverrides({
    @AssociationOverride(name = "pk.questionMaster", 
    joinColumns = @JoinColumn(name = "QUESTIONID", nullable = false)),
    @AssociationOverride(name = "pk.userDetails", 
    joinColumns = @JoinColumn(name = "USERID", nullable = false)) })
@XmlAccessorType(XmlAccessType.NONE)
@XmlRootElement(name = "answer")
public class QuestionAnswer {
private String answer;
private Date date;
private QuestionAnswerId pk = new QuestionAnswerId();

@EmbeddedId
public QuestionAnswerId getPk() {
    return pk;
}

@XmlElement (name = "question", type=QuestionMaster.class)
@Transient
public QuestionMaster getQuestionMaster(){
    return getPk().getQuestionMaster();
}

public void setQuestionMaster(QuestionMaster questionMaster){
    getPk().setQuestionMaster(questionMaster);
}

@Transient
public UserDetails getUserDetails(){
    return getPk().getUserDetails();
}


public void setUserDetails(UserDetails userDetails){
    getPk().setUserDetails(userDetails);
}

public void setPk(QuestionAnswerId pk) {
    this.pk = pk;
}

@XmlElement(name="ans")
@Column(nullable=false)
public String getAnswer() {
    return answer;
}

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

@XmlElement(name="date")
@Temporal(TemporalType.DATE)
@Column(name = "DATE", nullable = false, length = 10)
public Date getDate() {
    return date;
}

public void setDate(Date date){
    this.date = date;
}
}

QuestionAnswerId.class

@Embeddable
public class QuestionAnswerId implements Serializable {

private QuestionMaster questionMaster;
private UserDetails userDetails;

@ManyToOne (cascade = CascadeType.ALL, fetch = FetchType.EAGER)
public QuestionMaster getQuestionMaster() {
    return questionMaster;
}

public void setQuestionMaster(QuestionMaster questionMaster) {
    this.questionMaster = questionMaster;
}

@ManyToOne (cascade = CascadeType.ALL, fetch = FetchType.EAGER)
public UserDetails getUserDetails() {
    return userDetails;
}

public void setUserDetails(UserDetails userDetails) {
    this.userDetails = userDetails;
}
}

Sample REST API output for: /rest/users/5

<user>
    <userName>xyz</userName>
    <userId>5</userId>
    <answer>
        <ans>xyz</ans>
        <date>2013-12-18T00:00:00+05:30</date>
        <question>
            <ques>What is your name</quesId>
            <quesId>5</quesId>
        </question>
    </answer>
    <answer>
        <ans>25</ans>
        <date>2013-12-18T00:00:00+05:30</date>
        <question>
            <ques>What is your age</ques>
            <quesId>6</quesId>
        </question>
    </answer>
</user>

You comments/suggestions on this database design will be highly appreciated. I am new to Hibernate and in the process the learning it. Please suggest whether there is a better way to design the user-question-answer relationship, so that the response XML looks more elegant.

Answer 1

Rename QuestionAnser to QuestionAnswer but I think this is obvious..

IMHO your Entitynames are to complicated and contain duplicate answers. I would rename QuestionMaster to Question and QuestionAnswer to QuestionAnswer.

Why do you have an embedded ID?

Answer 2

Whether a mapping is correct cannot be answered without knowing more about the Domain/UseCase. Mapping above applies to both ORM and serialization mappings. ORM is a form of serialization/deserialization after all.

Due to different reasons, chief among them being that ORM and other mappings are still a sore point, developers try to make mappings reusable. Trying to make code more reusable, they try to make one set of mappings to rule them all. In most cases this is a false trade-off between reuse and separation of concerns. Instead it usually is just another case of Premature Generalization.

Analyzing your code in this light.

Assuming a use case of "View what answers a user gave to each question" or "view-user-answers" for short.

Starting with XML schema, since it is used for information interchange it is a given, we move from out side in.

But in the initial design we can make improvements. user/userName, user/userId repetition in element name. I find wrapping collections in a collection root element more readable. If there is a small number of questions client can cache them however it wants. We wouldn't need to pass question text and id at the same time. This is an engineering question. Also made element names more readable. If bandwith were a concern you might as well think other options than raw xml before making element names cryptic.

<user>
    <name>xyz</name>
    <id>5</id>
    <answers>
        <answer>
            <questionId>5</questionId>
            <text>xyz</text>
            <date>2013-12-18T00:00:00+05:30</date>
        </answer>
        <answer>
            <questionId>6</questionId>
            <text>25</text>
            <date>2013-12-18T00:00:00+05:30</date>
        </answer>
    </answers>
</user>

Above is an immutable data structure. These usually map to a Data Transfer Object. DTOs also are immutable. You can pretty much map your xml schema one-to-one to a Java class. In fact there are tools which do that automatically for you.

// packaging convention: company-url/project-name/use-case/sub-use-cases-or-mvc-layers
package com.example.questapp.questionaire.view_user_answers.more_packages_only_if_necessary;

// constructor getters equals etc usual suspects omitted for readability.
// this class is NOT mapped to database. 
// because database and xml schemas should be able to changed independently 
// XML mapping annotations goes here
class UserDTO {
    String name;
    int id;
    List<Answer> answers;

    static class Answer {
        int questionId;
        String text;
        Date date;  
    }
}

Note ...Details etc useless suffix. In fact I only added ..DTO suffix because java namespacing is crap and I plan to have two User classes in the same source file. Answer does not have the suffix becaus it can be referred to unambiguously as UserDTO.Answer.

We can call this just Service because services and use-cases map one-to-one.

package com.example.questapp.questionaire.view_user_answers;
// note no updater method
// note no class mapped do DB is present in this interface
interface Service {
    // would be called from /rest/users/id
    UserDTO getUser(int id);
    // would be called from /rest/users
    List<UserDTO> getUsers();
}

Another service for another use case can be.

package com.example.questapp.questionaire.record_answer;
// note no getter method
// note no class mapped do DB is present in this interface either
interface Service {
    void recordAnswer(userId, questionId, text, date);
}

Both use cases will work on the same Entities, though they do not need to.

In this use case there are two top level entities: User and Question. For this case an Answer given by a User to a Question belong to User.

We name our classes the SAME as above. We do not name our class UserDetails while we talk about Users. Similarly just Question instead of QuestionMaster and just Answer instead of QuestionAnswer. We do not name our classes not after tables.

private Collection<QuestionAnswer> listOfAnswers We do not repeat type in identifiers. Just answers is enough. Remember we assumed the use case was: "View what answers a user gave to each question". This suggest there is a one-to-one mapping between a Question and the Answer the User gave to it. So we use a Map. There probably is a natural ordering by questionId or question number whatever, and you using listOfAnswers hints at that so a SortedMap then.

// This is shared by both sub-use-cases 
// therefore it is in the upper level
package com.example.questapp.questionaire;

// ORM mapping is here
class User {
    int userId;
    String userName;
    SortedMap<Question, Answer> answers;

    // this is private since no one except hibernate should set it    
    private void setAnswers(Map<Question, Answer> answers);

    // this is the SAME name with service method
    // which is the SAME name with the use-case
    // which is the SAME name with what users call what they do.     
    public recordAnswer(Question question, text, date);
}

After renaming QuestionMaster to Question I renamed Question.question to Question.text because it is "the text of the Question", no? I also removed Question.answers as it is irrelevant in this use case. And we do not write code "just in case". Because You Ain't Gonna Need It.

// Simpler, no?
class Question {
    int id;
    // By naming this text
    String text;
}

Similarly:

class Answer {
    Id id;
    String answer;
    Date date;

    static class Id {
        // Now that we decided root entity for answer is User
        // We put it first.
        User user;
        Question question;
    }
}

This already is a long answer and is enough to digest. But I can make edits if questions are asked in comments.