is this database perfect or not ??..please help/guide me

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

is this database perfect or not ??..please help/guide me

Post by PHPycho »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello frens over there N senior sirs....
I had designed a database which i had shown below.....
I dont know is that perfect or not .....Please help/guide me to make the database perfect so that i could start coding.....
Here goes the database
[syntax="sql"]
CREATE TABLE `tb_academic_year` ( 
  `ay_id` int(255) NOT NULL auto_increment, 
  `academic_yr` varchar(255) NOT NULL default '', 
  PRIMARY KEY  (`ay_id`) 
); 

INSERT INTO `tb_academic_year` VALUES (1, '2061'); 
INSERT INTO `tb_academic_year` VALUES (2, '2062'); 

CREATE TABLE `tb_class` ( 
  `class_id` int(255) NOT NULL auto_increment, 
  `class_level` varchar(255) NOT NULL default '', 
  `section` enum('A','B','C','D') NOT NULL default 'A', 
  `class_room_no` varchar(255) NOT NULL default '', 
  `tr_id` int(255) NOT NULL default '0', 
  `ay_id` int(255) NOT NULL default '0', 
  `tot_students` int(255) NOT NULL default '0', 
  PRIMARY KEY  (`class_id`) 
); 

CREATE TABLE `tb_exam_type` ( 
  `et_id` int(255) NOT NULL auto_increment, 
  `et_rank` enum('1','2','3') NOT NULL default '1', 
  `et_name` varchar(255) NOT NULL default '', 
  PRIMARY KEY  (`et_id`) 
); 


INSERT INTO `tb_exam_type` VALUES (1, '1', 'FirstTerm'); 
INSERT INTO `tb_exam_type` VALUES (2, '2', 'SecondTerm'); 
INSERT INTO `tb_exam_type` VALUES (3, '3', 'FinalExam'); 


CREATE TABLE `tb_routine` ( 
  `rt_id` int(255) NOT NULL auto_increment, 
  `class_id` int(255) NOT NULL default '0', 
  `rt_day` enum('SUN','MON','TUE','WED','THU','FRI') NOT NULL default 'SUN', 
  `rt_period_rank` enum('1','2','3','4','5','6','7','8') NOT NULL default '1', 
  `sub_id` int(255) NOT NULL default '0', 
  `rt_time` varchar(255) NOT NULL default '', 
  `tr_id` int(255) NOT NULL default '0', 
  PRIMARY KEY  (`rt_id`) 
); 

CREATE TABLE `tb_score` ( 
  `score_id` int(255) NOT NULL auto_increment, 
  `st_id` int(255) NOT NULL default '0', 
  `exam_type` int(255) NOT NULL default '0', 
  `sub_id` int(255) NOT NULL default '0', 
  `score` int(255) NOT NULL default '0', 
  `remarks` varchar(50) NOT NULL default '', 
  PRIMARY KEY  (`score_id`) 
); 


CREATE TABLE `tb_score_alternative` ( 
  `socre_id` int(255) NOT NULL auto_increment, 
  `st_id` int(255) NOT NULL default '0', 
  `exam_type` int(255) NOT NULL default '0', 
  `sub_id_set` varchar(255) NOT NULL default '', 
  `mo_set` varchar(255) NOT NULL default '', 
  `remarks_set` varchar(255) NOT NULL default '', 
  `percentage` int(255) NOT NULL default '0', 
  `result` enum('P','F') NOT NULL default 'P', 
  PRIMARY KEY  (`socre_id`) 
); 


INSERT INTO `tb_score_alternative` VALUES (1, 6, 1, '1,2,3,4,5,6', '70,70,70,70,70,70', 'Good,Good,Good,Good,Good,Good', 70, 'P'); 


CREATE TABLE `tb_student` ( 
  `st_id` int(255) NOT NULL auto_increment, 
  `class_id` int(255) NOT NULL default '0', 
  `com_id` int(255) NOT NULL default '0', 
  `st_roll` int(255) NOT NULL default '0', 
  PRIMARY KEY  (`st_id`) 
); 


CREATE TABLE `tb_student_details` ( 
  `com_id` int(11) NOT NULL auto_increment, 
  `st_fname` varchar(100) NOT NULL default '', 
  `st_mname` varchar(100) NOT NULL default '', 
  `st_lname` varchar(100) NOT NULL default '', 
  `gender` enum('M','F') NOT NULL default 'M', 
  `st_email` varchar(200) NOT NULL default '', 
  `dob` varchar(255) NOT NULL default '', 
  `st_father` varchar(255) NOT NULL default '', 
  `st_mother` varchar(255) NOT NULL default '', 
  `st_perm_address` text NOT NULL, 
  `st_temp_address` text NOT NULL, 
  `st_contact_no` varchar(255) NOT NULL default '', 
  `joined_date` datetime NOT NULL default '0000-00-00 00:00:00', 
  `left_date` datetime NOT NULL default '0000-00-00 00:00:00', 
  PRIMARY KEY  (`com_id`) 
); 

CREATE TABLE `tb_subject` ( 
  `sub_id` int(255) NOT NULL auto_increment, 
  `class_level` int(255) NOT NULL default '0', 
  `sub_name` varchar(255) NOT NULL default '', 
  `sub_fm` int(255) NOT NULL default '0', 
  `sub_pm` int(255) NOT NULL default '0', 
  PRIMARY KEY  (`sub_id`) 
); 


CREATE TABLE `tb_teacher` ( 
  `tr_id` int(255) NOT NULL auto_increment, 
  `tr_fullname` varchar(255) NOT NULL default '', 
  `tr_email` varchar(255) NOT NULL default '', 
  `tr_gender` enum('M','F') NOT NULL default 'M', 
  `tr_qualification` varchar(255) NOT NULL default '', 
  `tr_jobtype` varchar(255) NOT NULL default '', 
  `tr_perm_address` text NOT NULL, 
  `tr_temp_address` text NOT NULL, 
  `tr_contact_no` varchar(255) NOT NULL default '', 
  `tr_joined_date` datetime NOT NULL default '0000-00-00 00:00:00', 
  `tr_left_date` datetime NOT NULL default '0000-00-00 00:00:00', 
  PRIMARY KEY  (`tr_id`) 
); 


CREATE TABLE `tb_user` ( 
  `user_id` int(255) NOT NULL auto_increment, 
  `link_id` int(255) NOT NULL default '0', 
  `username` varchar(100) NOT NULL default '', 
  `password` varchar(255) NOT NULL default '', 
  `ur_id` int(10) NOT NULL default '0', 
  `is_active` enum('1','0') NOT NULL default '1', 
  PRIMARY KEY  (`user_id`) 
); 


CREATE TABLE `tb_user_rank` ( 
  `ur_id` int(255) NOT NULL auto_increment, 
  `user_rank` enum('1','2','3','4','5') NOT NULL default '1', 
  `user_position` varchar(255) NOT NULL default '', 
  PRIMARY KEY  (`ur_id`) 
); 


INSERT INTO `tb_user_rank` VALUES (1, '1', 'SuperAdmin'); 
INSERT INTO `tb_user_rank` VALUES (2, '2', 'Admin'); 
INSERT INTO `tb_user_rank` VALUES (3, '3', 'ClassTeacher'); 
INSERT INTO `tb_user_rank` VALUES (4, '4', 'Student'); 
INSERT INTO `tb_user_rank` VALUES (5, '5', 'Others'); 


CREATE TABLE `tb_user_statistics` ( 
  `user_id` int(255) NOT NULL default '0', 
  `sesssion_id` varchar(255) NOT NULL default '', 
  `cookie_id` varchar(255) NOT NULL default '', 
  `ip` varchar(255) NOT NULL default '', 
  `browser` varchar(255) NOT NULL default '', 
  `time` int(11) NOT NULL default '0', 
  `is_login` enum('0','1') NOT NULL default '0', 
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00', 
  `login_times` int(255) NOT NULL default '0' 
);  
Concept of this site:
There is superadmin who manages the admin and class teachers

what a admin can do?
He can add, edit, delete student/teacher profiles, class, subject, routine , assign student n teachers password etc..

what student can do?
He can login with the password and view his details and mark sheet of the academic he passed...

what a teacher can do?
He can login with the password and view his details and his routine

what a class teacher can do?

He can edit their students' different terminal scores/marks etc.,

Problems i am facing ?
>How to generalize the routine , is that routine perfect or else should be modified
>How to generalize the score table so that it would be easy for retrieving n updating...i had made to score table namely tb_score and tb_score_alternative ...i dont think both are ok..help me to make a good score table..
>what happens when student goes from class 1 to class 2.....his st_id is made new but com_id remains the same ....is there any alternative solution for this
>How should the class teacher manages his students score .....
............
Please help me on the above topics...Anyway thnks for showing the patience while reading this...Thanks in Advance


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

Hi - it looks generally pretty good, but sometimes I think you are perhaps over-using the auto_increment attribute in forming a primary key.

For example, in your tb_score, you have score_id as an auto_increment, which you use as a primary key. I can't envisage a situation where you would actually need to search by that primary key - I'm fairly sure that you would usually search by st_id, exam_type and sub_id in order to return the score of a particular student.

Obviously, not knowing exactly how you are going to use your tables, it's difficult to say exactly which way is best, but I personally would have my tb_score table looking more like:

Code: Select all

CREATE TABLE `tb_score` ( 
  `st_id` int(255) NOT NULL DEFAULT '0', 
  `exam_type` int(255) NOT NULL DEFAULT '0', 
  `sub_id` int(255) NOT NULL DEFAULT '0', 
  `score` int(255) NOT NULL DEFAULT '0', 
  `remarks` varchar(50) NOT NULL DEFAULT '', 
  PRIMARY KEY  (`st_id`, `exam_type`, `sub_id`) 
);
Personally, I find that auto_increment keys are best used when there is no other way to form a unique key on a table - for example, tr_id - it is not impossible (very very very unlikely, perhaps) that there will be two teachers with the same name, address, contact number, address blah blah. In this case, it is correct to use an auto_increment, however, in your table tb_user_rank, your primary key should be user_rank. In effect, you've got a redundant column, in that the data is repeated twice - once in the auto_increment key, and again in the user_rank column. In this case, the primary key can be the user_rank.

One thing I would NOT do, is expect a data field to accept multiple data items, like your tb_score_alternative table is doing:

Code: Select all

INSERT INTO `tb_score_alternative` VALUES (1, 6, 1, '1,2,3,4,5,6', '70,70,70,70,70,70', 'Good,Good,Good,Good,Good,Good', 70, 'P');
This to me seems wrong, but again, I'm not sure exactly what you are doing with this. I would personally have another table for the "remarks", keyed on (st_id, exam_type, sub_id, remark). Generally - apart from in specific circumstances, (which I've never come across) - it is not good practice to hold many data items in one field. What happens when a teacher inserts a comment like "good, but could do better" ? How will your database deal with the fact that there is a comma in the data?

One other observation - what is the difference between the various student ids? Why is there st_id, com_id? I think your student_details table should maybe be keyed on st_id.

In summary - I think you should have a look at your selection of primary keys, and I would opt for a score table that was more like tb_score, and not like tb_score_alternative.

I hope some of this is useful,

GM
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

thnks for the reply..

Post by PHPycho »

First of all i wanna thank you for the reply and for your valueable help.....

Thanks for the concept of auto_increment...
Here i want to clearify some facts .......
>>In tb_student i had used the com_id ....this is used because when a student goes to upper grade/class then he gots the new st_id so that his old id is safe to retrieve his past performances..but his com_id is same because when he goes to upper grade his details ...address...are same ...and there i s no need of reentry of student details
>>In the tb_user i had used the link_id to link with the respective st_id and tr_id . I would have used only user_id but problem may occur when a student goes to upper grade...
>>I would also prefer to tb_score rather than tb_score_alternative but there would be difficulties in inserting and fetching a particular students score ..and it is also difficult to show the Result (pass/failed) , percentage in the tb_score ..is there any alternative for the best general table for tb_score
.............................................................................I
I hope u got my point a bit....please help if possible on the above topics...
Anyways thanks a lot for those who read n replied my problems...
Post Reply