one to many annotation mapping with composite key

Question

I have two tables as mentioned below. The point is to have 'n' choice for my question. I have designed to have composite key on the second table instead of having one more column for primary key. With this approach i have two questions.

1. Is this a good approach in table design? i mean TABLE QUESTIONCHOICE has composite key rather than a primary key.

2. How do i make one to many mapping in Hibernate between QUESTIONBANK and QUESTIONCHOICE tables using annotations?

Any points and suggestions will be of great help.

Thanks,

-Vijay Selvaraj

CREATE TABLE QUESTIONBANK(
    QUESTIONID INT NOT NULL AUTO_INCREMENT(10001, 1),
    QUESTION VARCHAR(200) NOT NULL,
    TOPIC VARCHAR(20) NOT NULL,
    SUBTOPIC VARCHAR(20) NOT NULL,
    COMPLEXITY SMALLINT  NOT NULL DEFAULT 1,
    QUESTIONTYPE SMALLINT  NOT NULL,
    VERSION INT NOT NULL DEFAULT 0,
    CONSTRAINT QUESTIONBANK_PK PRIMARY KEY (QUESTIONID)
);

CREATE TABLE QUESTIONCHOICE(
   QID INT NOT NULL,
   CHOICE VARCHAR(100) NOT NULL,
   CORRECT_CHOICE BOOLEAN, 
   VERSION INT NOT NULL DEFAULT 0,
   CONSTRAINT QUESTIONCHOICE_PK PRIMARY KEY (QID, Choice),
   CONSTRAINT QUESTIONCHOICE_FK FOREIGN KEY (QID) REFERENCES QUESTIONBANK (QUESTIONID)
);

Answer 1

Let me answer the first half of your question...

1. Is this a good approach in table design? i mean TABLE QUESTIONCHOICE has composite key rather than a primary key.

This is not an either-or proposition. You have a key that happens to be both composite and primary.

Whether your design is valid depends on a whether a same value of CHOICE should be able to exist in multiple QUESTIONCHOICE rows (connected to different questions):

• If yes, your design is correct.
• If no, it's incorrect and you should make the CHOICE alone a key.

Whether to also add a surrogate key is a matter of balance, and here are some criteria to help you make a decision.