Database design for a student registration system!

[joyagopian90]

Hi I am a senior in business computing and I have a project to hand to my university in order to graduate and I have chosen a student registration system but in order to accomplish that I obviously need a well designed and organized database. here are the tables I have done so far:
a COURSE table that contain:
-courseid int autoincrement
-coursename varchar(50)
-describtion varchar (255)
-active varchar (50)
-credits int
-teacherId int
-Room varchar (50)
-day1 varchar(50)
-time1 varchar(50)
-day2 varchar(50)
-time2 varchar (50)

a PERSONS table:

id , firstname , lastname, username ,password,email,gender,address,phone,mobile,roleid

a Roles table :
roleid,rolename,describtion

the objective is to let students regester online with the following conditions
- a student is not allowed to register more than 35 credits
- a class can only hold 70 students (for example)
- only the administrator can accept the student's registration or decline it
- the administrator can add courses

also I would like to ask about the persons table I have made should I make a seperate teacher table or will the persons table be okay

thank you for your answers in advance I know it's a long question but please help me!!!

The only question you've asked is whether you need a separate teacher/student table. I'd go with just one table.

So what else do you want to know?

[joyagopian90]

The only question you've asked is whether you need a separate teacher/student table. I'd go with just one table.

So what else do you want to know?

first of all thank you for your reply.
second my questions are:
-what other tables I may need to complete the aplication?
-every student will have a separate scheduale how do I do that ? do I query it from the existing table or do I create a seperate table?
-about the persons table in order to link teacherId and personsId how do I do that knowing that personsId include teachers and students!
thank you for your answer.

[NinjaMediaD]

What you need is a linking table, because of the situation, a student can take many courses, and each course can have many students.
(roll of student is played by the person who is not the teacher)
Do yourself a favor, Draw it out.

I think if I gave you anything more, I might be giving away the answers.

Is this a standard school/college project as I seem to have answered loads of threads on this recently.

Your courses table looks a bit limited.
I wouldn't put days/times there, I'd place it in a separate table:

course_periods
course_period_id
course_id
weekday (or date??)
start_time
end_time

THis way be add more flexibility to the courses - e.g. some may have 3 days, some 1 day. You don't want to be messing with table structure just because the course requirements change.

Also the teacher_id may be better served in another link table, in the event that more than one teacher delivers the course:

course_teacher
course_teacher (optional)
course_id
user_id

In addition the room_id may not be appropriate here too. If the course occurs over more than one session, it's conceivable that locations may change, so, back to the course_periods:

course_periods
course_period_id
course_id
weekday (or date??)
start_time
end_time
room_id

rooms
room_id
room_label
room_map_url

=======================

[joyagopian90]

Thank you guys for your help I think this data will help me get started thank you so much and if I have any question I hope You guys can help me
thank you again