i am creating a marks management system using php & mysqlwhere the concerned faculty will be able to login and enter the marks of the students. i can go with a simple table but the problem is there are 288 different subjects for which marks must be entered. So creating a mysql table with so many subjects does not look good for me. please suggest me the best way to manage user permissions so that only the corresponding faculty will be able to enter marks
A:
Table <Subjects>
- List item
- SubjectId
- SubjectName
- SubjectTeacher
- .
- .
Table <Marks>
- SubjectId
- Date
- Marks
- .
- .
........... This assumes that you have only one teacher per subject. If you want multiple intructors per subject, remove the SubjectTeacher column from the Subjects table and make two new tables
Table <SubjectInstructor>
- SubjectId
- IntructorId
Table <Instructor>
- InstructorId
- InstructorName
- . .
Stewie
2010-09-07 03:17:37
This is good overall advice however you should avoid using mixed case for table/column names - keep them all lowercase and use underscores to separate words. Also, i like to use `id` as the primary key for a table, and then if something is a fk to another table use `foreign_id` where "foreign" is the name of the foreign table. This makes it easier to tell what is going on when writing queries.
prodigitalson
2010-09-07 03:28:25
i got it. but is there no other way to manage permissions like: there will be a single table in which there is a permissions column where there are subject ids seperated by some underscore or something....? please let me know if it is possible
2010-09-07 03:29:09
@prodigitalson yes, you are right. I have been using CamelCase in my current company (extending a legacy sybase DB) and am used to the (Not-so-good) syntax. My projects in MySQL do follow the standards you just gave an example of.
Stewie
2010-09-07 03:43:13
@user420528 You can do a single table. If you have only ONE set of marks per subject ( this means, you will have to overwrite previous marks) , you can do: subject_id, subject_name, instructor_id, instructor_name, marks, last_updated. While inserting you make a query like: UPDATE Subject SET marks=99 WHERE subject_id = 12 AND instructor_id=14
Stewie
2010-09-07 03:45:09
+1
A:
This is a fairly big and hairy problem; you do know that, right? It is good you're not regarding security as an after-thought, but I don't think it's security you're asking about.
I spent some time developing an academic management system a few years ago. Some of the design required was fairly involved. As well as subjects and students, we quickly found we needed a class structure so we could say this student was in this subject this term, but wasn't next term. And to say that this teacher taught this class this subject this term, but another teacher taught this other class the same subject this term.
That also meant we had to keep some information about when classes were, which turned into a timetable mechanism. (I won't go into that, though, as it was very involved.) And then we had to keep multiple sorts of marks for a student in a class, which created multiple task types, only one of which was end-of-term exams.
Finally, we abstracted our students and teachers into generic contacts. This allowed a permission system to generalise for editing objects they "owned". It also let us support advanced concepts like a former student returning to teach. And for all contacts to login in various capacities.
staticsan
2010-09-07 04:15:11