Database design of online written test system

Test paper Template: papertemaplate

Job Type: PositionType

Question Bank: question

Answer: Sheet

Candidate answers (answer list): Ansersheet

User table (includes interview, HR, candidate): User

Roles table: Role (the type used to differentiate users)

The relationship between the test paper template and the question Bank (test list): Paperdetails

Relationship between candidate and post: userpositiondertails

1. User table (user table contains 3 roles) user

Column Name	Meaning	Type	Property
Id	Record number	Int	Self-increment, primary key, non-null
LoginName	User name	VARCHAR (20)	Unique, non-empty
Password	Password	VARCHAR (40)	Non-empty
Email	Mailbox	VARCHAR (50)	Non-empty
Username	real name	VARCHAR (20)	Non-empty
Phone	Phone number	CHAR (11)	Non-empty
Creator	Create Person ID	Int	FOREIGN key
Lastupdator	Last Modified person ID	Int	FOREIGN key
Createtime	Creation time	TIMESTAMP
LastUpdateTime	Last Modified Time	TIMESTAMP
Roleid	Role ID	Int	Non-null, foreign key
Identification	Id	Char(18)
Sex	Gender	Varchar (8)
Recruittype	Type of recruitment	Varchar (40)
Recycle	Whether to freeze	tinyint (1)	0 not frozen, 1 frozen
Unit	Unit or institution	Varchar (100)

Roles table: Role

Column Name	Meaning	Type	Property
Id	Record number	Int	Self-increment, primary key, non-null
RoleName	Role name	Int	Unique, non-empty

Userpositiondertails

Id	Record number	Int	Self-increment, non-null, primary key
Userid	User number	Int	Non-null, foreign key
PositionID	Post Number	Int	Non-null, foreign key

Note: The user number includes the interviewer and the applicant

1. Paper Template table Papertemaplate

Id	Quiz template Number	Int	Self-increment, non-null, primary key
Papername	Test paper Name	VARCHAR (40)	Unique, non-empty
Editorid	Editor ID	Int	Non-null, foreign key
Score	Total questions	Int
typeID	Test Post Type	Int	Non-null, foreign key
EditTime	Edit Time	TIMESTAMP	Index
Recycle	Whether to recycle	tinyint (1)	Non-NULL, default 0 (0 not reclaimed, 1 recycled)
Mark	Label	VARCHAR (255)

3 Post Type Table PositionType

Id	Post Type number	Int	Self-increment, non-null, primary key
TypeName	Job Type name	VARCHAR (40)	Unique, non-empty

4. Question Bank question

Id	Question number	Int	Self-increment, non-null, primary key
Question	Problem dry	Varchar (1000)	Non-empty
Score	Scores	Int	Non-empty
Referanswer	Reference answer	Varchar (1000)
Standard	Scoring criteria	Varchar (1000)	Non-empty
Createtime		TIMESTAMP

5. Test List Paperdetails

Id	Paper Detail Number	Int	Self-increment, non-null, primary key
Paperid	Quiz number	Int	Non-null, foreign key
QuestionID	Question number	Int	Non-null, foreign key
Number	The number of the question in the test paper	Int	Non-empty

Answer sheet Sheet

Id	Answer number	Int	Self-increment, non-null, primary key
Assignerid	Assigned Person ID	Int	Non-empty
Interviewid	Interviewer ID	Int	Non-empty
Candidaterid	Candidate ID	Int	Non-empty
Paperid	Test paper Template ID	Int	Non-empty
Createtime		TIMESTAMP
Taskstarttime	Task Start Answer time	Datetime	Non-empty
Taskendtime	Task End Answer Time	Datetime	Non-empty
Answerstarttime	Really start answering time	Datetime
Answerendtime	Real end of answer time	Datetime
Checktime	Time	Datetime
Logintime	Login system Time	Datetime
Recycle	Whether to recycle	tinyint (1)	Non-NULL, default 0 (0 not reclaimed, 1 recycled)
IsLogin	Whether to login system	tinyint (1)	Non-NULL, default 0 (0 not logged in, 1 logged in)
Issubmit	Whether to submit papers	tinyint (1)	Non-NULL, default 0 (0 uncommitted)
Isanswer	Whether to answer	tinyint (1)	Non-NULL, default 0 (no answer 0, 1 answer)
Ischeck	Whether to review	tinyint (1)	Non-NULL, default 0 (not reviewed 0, 1 reviews)
Comment	Total reviews	Varchar (1000)

Questionnaire Ansersheet (Answer table)

Id	Answer detail number	Int	Self-increment, non-null, primary key
QuestionID	Number of topics in the answer sheet	Int	Non-empty
sheetID	Answer ID	Int	Non-null, foreign key
Score	Score	Int
Comment	Interviewer comments	Varchar (1000)
Answer	Answer	Varchar (1000)