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'
); 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]