samedi 18 juin 2016

Better way to design database for online assesment

I am working on Online Exam module, initially i have created the database and it's work fine when the number of questions are fixed, in my case i have 10 Questions and multiple users,now the requirement is different i want to conduct exam of 50 questions so what should i do or any time an admin can change the number of question as per the requirement

CREATE TABLE `test_sheet` (
`user_id` varchar(200) NOT NULL,
`q1` int(10) DEFAULT '0',
`q2` int(10) DEFAULT '0',
`q3` int(10) DEFAULT '0',
`q4` int(10) DEFAULT '0',
`q5` int(10) DEFAULT '0',
`q6` int(10) DEFAULT '0',
`q7` int(10) DEFAULT '0',
`q8` int(10) DEFAULT '0',
`q9` int(10) DEFAULT '0',
`q10` int(10) DEFAULT '0',
`ip_address` varchar(100) DEFAULT 'Not Available',
`score` int(50) DEFAULT NULL,
`start_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`random_question` varchar(500) DEFAULT NULL,
`passedTime` varchar(500) DEFAULT NULL,
`remainTime` varchar(500) DEFAULT NULL,
`last_update_time` varchar(500) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now test_sheet table like this

insert  into `test_sheet`(`user_id`,`q1`,`q2`,`q3`,`q4`,`q5`,`q6`,`q7`,`q8`,`q9`,`q10`,`ip_address`,`score`,`start_time`,`random_question`,`passedTime`,`remainTime`,`last_update_time`) values ('demo@123',0,0,0,0,0,0,0,0,0,0,'Not Available',0,'2016-06-11 14:46:52','1,5,4,2,3,10,9,7,6,8','59','5258','2016-06-11 14:47:25');

i have an alternate option like this

User_ID   ||  Q_ID    ||  ANS_ID 
demo@123  ||    1     ||   4
demo@123  ||    2     ||   3
demo@123  ||    3     ||   2
demo@123  ||    4     ||   4
demo@123  ||    5     ||   1
Xyzz@123  ||    1     ||   2
Xyzz@123  ||    2     ||   4
Xyzz@123  ||    3     ||   2
Xyzz@123  ||    4     ||   1
Xyzz@123  ||    5     ||   3

now i have 50 or more questions and user for exam May be 300 so the rows will be approx 15000 or more so what is the better way or any other idea

Aucun commentaire:

Enregistrer un commentaire