Multiple choice tests data structure (sql)

[Predatorkill]

Καλησπερα παιδες, θελω να φτιαξω ενα multiple choice quiz και εχω σκεφτει αυτο το schema, πιστευετε οτι ειναι ενταξει; 

θελω να το κανω οσο πιο robust γινεται και σκεφτομαι να βαλω και ιστορικο των τεστ, δηλαδη ο χρηστης να μπορει να δει  το ιστορικο του ανα τεστ αλλα και σε ποιες ερωτησεις δυσκολευτηκε (την απαντησε πολλες φορες λαθος) ή οτι αλλο πιστευετε οτι μπορω να ενσωματωσω.

επισης καλο θα ηταν να μαρκαρω ποιες ερωτησεις εχουν τις πιο πολλες λανθασμενες απαντησεις απο τους χρηστες ωστε να μπορω να δημιουργω ενα «δυσκολο» τεστ με βαση αυτες τις ερωτησεις.

Η βαση θα ειναι postgresql 13.

sql script:

CREATE SEQUENCE "public".choice_id_seq START WITH 1;

CREATE SEQUENCE "public".question_id_seq START WITH 1;

CREATE SEQUENCE "public".test_id_seq START WITH 1;

CREATE SEQUENCE "public".user_id_seq START WITH 1;

CREATE  TABLE "public".questions ( 
	id                   bigint DEFAULT nextval('question_id_seq'::regclass) NOT NULL ,
	question             varchar(100000)  NOT NULL ,
	image                varchar(5000)  NOT NULL ,
	thumbnail            varchar(5000)  NOT NULL ,
	created_at           timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	updated_at           timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	is_active            boolean DEFAULT true NOT NULL ,
	CONSTRAINT pk_questions_id PRIMARY KEY ( id )
 );

CREATE  TABLE "public".tests ( 
	id                   bigint DEFAULT nextval('test_id_seq'::regclass) NOT NULL ,
	created_at           timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	updated_at           timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	difficulty           integer DEFAULT 1 NOT NULL ,
	name                 varchar(100)  NOT NULL ,
	CONSTRAINT pk_tests_id PRIMARY KEY ( id )
 );

CREATE  TABLE "public".tests_and_questions ( 
	question_id          bigint   ,
	test_id              bigint   
 );

CREATE  TABLE "public"."user" ( 
	name                 varchar(100)  NOT NULL ,
	email                varchar(100)  NOT NULL ,
	"password"           varchar(100)  NOT NULL ,
	id                   bigint DEFAULT nextval('user_id_seq'::regclass) NOT NULL ,
	"createdAt"          timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	"updatedAt"          timestamptz DEFAULT CURRENT_TIMESTAMP  ,
	"role"               varchar(50) DEFAULT 'USER'::character varying  ,
	CONSTRAINT pk_users_id PRIMARY KEY ( id )
 );

CREATE  TABLE "public".question_choices ( 
	choice               varchar(5000)  NOT NULL ,
	id                   bigint DEFAULT nextval('choice_id_seq'::regclass) NOT NULL ,
	question_id          bigint  NOT NULL ,
	is_correct           boolean DEFAULT false NOT NULL ,
	times_answered       bigint   ,
	is_active            boolean DEFAULT true NOT NULL ,
	CONSTRAINT pk_question_choices_id PRIMARY KEY ( id )
 );

CREATE  TABLE "public".user_question_answer ( 
	user_id              bigint  NOT NULL ,
	question_id          bigint  NOT NULL ,
	choice_id            bigint  NOT NULL ,
	is_correct           boolean   ,
	created_at           timestamptz DEFAULT CURRENT_TIMESTAMP  
 );

ALTER TABLE "public".question_choices ADD CONSTRAINT fk_question_choices_questions FOREIGN KEY ( question_id ) REFERENCES "public".questions( id );

ALTER TABLE "public".tests_and_questions ADD CONSTRAINT fk_tests_and_questions_questions FOREIGN KEY ( question_id ) REFERENCES "public".questions( id );

ALTER TABLE "public".tests_and_questions ADD CONSTRAINT fk_tests_and_questions_tests FOREIGN KEY ( test_id ) REFERENCES "public".tests( id );

ALTER TABLE "public".user_question_answer ADD CONSTRAINT fk_user_question_answer_question_choices FOREIGN KEY ( choice_id ) REFERENCES "public".question_choices( id );

ALTER TABLE "public".user_question_answer ADD CONSTRAINT fk_user_question_answer_questions FOREIGN KEY ( question_id ) REFERENCES "public".questions( id );

ALTER TABLE "public".user_question_answer ADD CONSTRAINT fk_user_question_answer_users FOREIGN KEY ( user_id ) REFERENCES "public"."user"( id );

Επεξ/σία 1 Μαρτίου 2021 από Predatorkill

[Kercyn]

Μία απάντηση δε θα μπορούσε να κολλάει σε παραπάνω από μία ερωτήσεις; Το λέω μήπως βγάλεις λάθος νούμερα έτσι.

[Predatorkill]

4 λεπτά πριν, Kercyn είπε

Μία απάντηση δε θα μπορούσε να κολλάει σε παραπάνω από μία ερωτήσεις; Το λέω μήπως βγάλεις λάθος νούμερα έτσι.

Οχι θα ειναι εντελως fixed και καθε ερωτηση θα εχει τις δικες της απαντησεις, κι ας υπαρχει αλλη ομοια απαντηση.

[MastroGiannis]

Καταρχάς, το is_correct επαναλαμβάνεται στον user_question_answer, μπορείς να το πάρεις από τον question_choices.

Αντί για user_id και question_id στον user_question_answer, θα έπρεπε να έχεις ένα user_test_id, το οποίο να αναφέρεται στο PK ενός πίνακα user_tests τον οποίο δεν βλέπω στο σχήμα.

Τώρα, όπως έχεις τον user_question_answer, μπορούν να τεθούν απαντήσεις από άλλες ερωτήσεις (many to many) για κάποιο άγνωστο test του user (εκτός κι αν το προορίζεις για ένα test/user) και το is_correct του να μη συμφωνεί με το is_correct του question_choices.

Θεωρώ πως, ο user_question_answer, ως βασικά πεδία θα έπρεπε να έχει τα εξής:

user_test_id

choice_id

που θα μπορούσαν να αποτελούν και το κλειδί του.

Αυτά για την ώρα.

Edit:

Επίσης, το times_answered, θα πρέπει να είναι πεδίο ερωτήματος και όχι πίνακα.

Επεξ/σία 2 Μαρτίου 2021 από MastroGiannis

[k33theod]

Ωραία ερώτηση, θα μπορούμε να μιλάμε ένα μήνα. 

Κατ αρχήν για τον πίνακα ερωτήσεις. Γνώμη μου είναι η κάθε ερώτηση να ανήκει σε μια κατηγορία (Αθλητικά, μουσική κλπ) και ένα  target group, ηλικιακό η άλλο, πχ μαθητής Γυμνασίου, προγραμματιστές.

Ο βαθμός δυσκολίας κατά τη γνώμη μου είναι θέμα ερώτησης ο βαθμός δυσκολίας του τεστ μπορεί να βγει από τον βαθμό δυσκολίας των ερωτήσεων.

Δεν πρέπει να υπάρχει σχέση user question, αλλά user test. Ο χρήστης κάνει ένα ολοκληρωμένο τεστ και μάλιστα μπορεί να το ξανακάνει αν πχ αποτύχει η θέλει να βελτιώσει το σκορ του.

Αυτά για την ώρα

 

 

[pmav99]

To naming convention στο screenshot/schema δεν είναι συνεπές. Ή `id` ή `<table_name>_id` όχι και τα δύο. Προσωπικά θεωρώ το `id` σημαντικά υποδεέστερο, αλλά η μίξη των δύο στυλ είναι σίγουρα η χειρότερη επιλογή

Επεξ/σία 3 Μαρτίου 2021 από pmav99