Hello there!
I'm building a web app to that manages quizzes. I have three tables:
Questions, QuestionBanks, and QuestionTypes. Every QuestionBank contains
several questions and each question will have a certain type and some
content. Below is the sample schema I'm using:
CREATE TABLE IF NOT EXISTS QuestionBanks(
id INT IDENTITY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS QuestionTypes(
id INT IDENTITY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS Questions(
bank_id INT NOT NULL,
id INT NOT NULL,
type_id INT NOT NULL,
content JSON NOT NULL,
answer JSON NOT NULL,
PRIMARY KEY (bank_id, id),
CONSTRAINT fk_bank FOREIGN KEY (bank_id) REFERENCES QuestionBanks(id),
CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES QuestionTypes(id)
);
I'm having trouble while creating a new QuestionBank row. When I'm
inserting new Questions, I want to be able to also create entries for any
new QuestionTypes that are encountered. Basically, what I want is:
if (NEW_TYPE is not present in QuestionTypes) {
insert NEW_TYPE into QuestionTypes
}
type_id = get ID for NEW_TYPE
Then I will use type_id to insert questions into the Questions table.
Please suggest a solution for this.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/58708ed6-6d84-44d6-bd8e-4953c01363b3%40googlegroups.com.
