Whether my database structure is right or wrong?

[guravharsha]

Hi;
This is my first project. I am developing a project: student feedback system for an institution.
Here each student will get an userid and password .
There are six tables in database to support project
1. User—consist of user information like username, password etc.
2. Groupid—consists of various groups like group of student, group of faculty. Users are divided into various groups.
3. questionid- consists of question of various types. Each question-id consist multiple question
4. Questionbank: consist of large no. of question for particular question-id. Here you can assume question-id=questiontype. Common between above two table is question-id(int).
5. event table—where various event are published. For each event there may be more than one question-id
e.g event—feedback of semester –4-- containing question-id like: question-id for economics batch, question-id for commerce batch
6. Final output table- consist of userid,question-id,question,answer marked by various user.

Once normal user will log in, it will shows all the event that is marked as published and targeted to the particular group. When it will click on this event it will show the various question-id (consist of various question)available for answering.
The question is in choice format. Like:
1.question name
a.option -A
b.option-B
once user answer all the questions it will stored the final output table.
here is an table structure:

CREATE TABLE groupid
(
 groupid   int(4)  NOT NULL  auto_increment,
 description varchar      varchar(100)  NOT NULL
  PRIMARY KEY ( groupid)
);

CREATE TABLE user
(
 userid   int(4)  NOT NULL  auto_increment,
 firstname  varchar(100)  NOT NULL,
 lastname  varchar(100)  NOT NULL,
 emailid      varchar(100)  NOT NULL  UNIQUE,
 password   varchar(50)  NOT NULL,
 groupid   int(8)  not null,
  PRIMARY KEY ( userid)
);
CREATE TABLE  Event
(
  Eventid     int(4)  NOT NULL  AUTO_INCREMENT,
  Description   varchar (255)  NOT NULL,
  Publish       varchar(20)   NOT NULL,
  Questionid   int(4)    NOT NULL,
  Targetgroup   varchar(70)   NOT NULL,
   Anonymous  varchar(15)   NOT NULL,
   PRIMARY KEY (  Eventid )
);
CREATE TABLE  Questionid
(
    Questionid   int(4)    NOT NULL  AUTO_INCREMENT,
     Description  varchar(70)   NOT NULL,
     Type    varchar(70)    NOT NULL,
     PRIMARY KEY (Questionid)
);
CREATE TABLE  QuestionBank
(
  Qserialno    int(3)  NOT NULL   AUTO_INCREMENT,
 Questionid   int(4)  NOT NULL ,
 Questionname  varchar(255)  NOT NULL,
  OptionA    varchar(50),
 OptionB    varchar(50),
 OptionC    varchar(50),
 OptionD    varchar(50),
 Other        varchar(50),
 Answer     varchar(1),
 PRIMARY KEY (Qserialno, Questionid  )
);
CREATE TABLE Final
(
 Userid    int(4) ,
 Eventid   int(4)  NOT NULL,
 Questionid  int(4)   NOT NULL,
 Qserialno    int(3)   NOT NULL,
 Answer     varchar(8)  NOT NULL
);

The colored column contain same data.
Thanks and regards
Haresh

[peter_budo]

When I directed you to Database Design section of the forum I was hopping some "DB monkey" will take care of the business. However it does look like we will have to trade ideas...

OK, I have here few questions and suggestions that I need to get answered:

1. Does the user table gone hold only students details or also administrator details?
   • If it will hold both, how will you distinguish between student and admin?
   • If you planning to have separated table for each of them, do you have any specific idea about login implementation?
2. In the user table you have groupid. Can the user be only member of one group or many groups?
3. I feel like Questionid table and QuestionBank (described in 4th point) should be more flexible. I would prefer to call Questionid table Questioners or Surveys and have it following structure
   • surveyid - primary key, created from groupid+school year+additional number of test/survey (not required if only one per year)
   • groupid
   • description (survey/test title)
   • type - questioner(not marking) in class test(require marking)
4. As mentioned before I would like to make it more flexible therefore I would drop idea of single question bank and create multiple tables with the surveyid as actual table name. This way
   • Query doesn't have to search whole question bank in order to locate questions related to selected survey
   • If somebody from admin/teachers mess up survey table they will not damage rest of the surveys
   • It will make easier to remove old surveys from the system

   The structure will be

   • questionNo - it is not necessary as you can do rows count
   • question
   • optionA
   • optionB
   • optionC
   • optionD
   • other
   • correctAnswer - if one or many of the A-D
5. In same manner I would drop Final table and replace with surveyid+result with structure where number of "q+number" is dependent on number of questions in surveyid
   • userid
   • taken - boolean true/false as for survey taken or not
   • q1 - submitted answer
   • q2 - submitted answer
   • score - final mark if it is test for marking or not to create this field when the table is created

The whole concept can be extended further, but I do not want to over-kill and just trying to provide suggestions for possible improvements. Ask questions, I will do my best answer them and shape this design if we will agree upon it.