Whether my database structure is right or wrong?
Please support our Database Design advertiser: Programming Forums
Thread Solved
 |
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:
The colored column contain same data.
Thanks and regards
Haresh
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 );
Thanks and regards
Haresh
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:
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.
OK, I have here few questions and suggestions that I need to get answered:
- 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?
- In the user table you have groupid. Can the user be only member of one group or many groups?
- 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)
- 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
- 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
- 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.
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, JAVAWUG (Java Web User Group), The London Android Group
Publilius Syrus
(~100 BC)
LJC - London Java Community, JAVAWUG (Java Web User Group), The London Android Group
|
Similar Threads
Other Threads in the Database Design Forum
- java project database error (Java)
- VB > Access2000 Database Upgrade Path (Visual Basic 4 / 5 / 6)
- python mysql doesnt work?plz help (Python)
- An OpenSource Database (C++)
- Please help me in connecting JSP to MySql (JSP)
- data sorts in reverse order!! help!! (C)
- problem again (C)
Other Threads in the Database Design Forum
- Previous Thread: Different DataBases for a Single Project
- Next Thread: Problem with MySQL Query
•
•
•
•
Views: 1107 | Replies: 1 | Currently Viewing: 1 (0 members and 1 guests)
Linear Mode
