Student Enrolment database

[Sawyer05]

I'm designing a Database for a department in a college and could use a lot of help.



The database is more complicated than i had initially thought.

I'll try explain it as best i can:

• The Database is for students with additional support needs.
• There are 2 ways to apply for a course, Through a School Link and Independantly
• Before they can enrol on a course, some candidates will attend the college for 2 days which is known as a 'Taster'.
• There are 3 different types of Tasters, which are determined by the type of Link the candidate has applied through:

1. Supported Link: Candidates attend for 2 consecutive Mondays
2. Partnership Link: Candidates attend for 2 consecutive Tuesdays
3. Independent Link: Candidates attend for 2 consecutive Wednesdays or Thursdays (due to high numbers from this type of link)

• The Taster is to let the candidates experience the college and lets the tutors determine what course is best suited for the student

Not All Candidates attend the taster




Now, my problem:

I have a Candidate Table
I.D (not student I.D as they don't receive that I.D until they are accepted onto a course)
First Name
Last Name
Address details
Emergency contact details
School info
Type of Link (drop down)
Taster? (Check Box)
Application Status (drop down: Accepted, Rejected, Pending)

i'll need the database to determine the following
If taster = True, type of link = whatever type then assign to that appropriate day
Then once the taster is completed, determine if the student will be offered a course and if they are and it is accepted by the student then move to that course.

Or if Application Status = Accepted (not attending taster) then move record to a new table where course can be chosen.


Any help i can get on this will be VERY appreciated as i'm feeling quite lost.

If you need any more details just ask and I'll answer as best I can

[June7]

Students enroll for a single course? Doesn't the college already have a database for tracking students? Why duplicate that? The 'taster' is event in only your department, not college?

[Sawyer05]

Hi June7 thanks for the reply,

The department is in control of courses specifically designed for students with additional support needs that use their own seperate database for tracking these students.

The department is currently using over 5 database files that means information has to be retyped constantly.

yes the Taster is only for this department

[June7]

This db is to consolidate 5 existing dbs?

These courses are not included in the college's main student database? They don't apply toward degree? They don't show on transcript?
Again, each student will enroll in only one course?

Consider tables:

CandidateInfo
ID (PK)
StudentID (to be completed if student enrolls, otherwise blank or can show Rejected or Pending - no need for ApplicationStatus field)
CourseID (if each student will enroll in only one course)
TypeOfLink (left blank or "None" if does not select - don't really see need for Taster yes/no field unless it is to indicate whether or not candidate actually attended the selected taster, in which case call it AttendTaster)
etc.

Courses
CourseNum (PK)
CourseName

StudentCourses (this table if student can enroll in more than one course, in which case the CourseID field would not be in StudentInfo)
StudentID (FK)
CourseID (FK)

Note that with this structure, the candidate record is not 'moved' to Course table. Moving records is bad design. Use table relationships and queries that join tables to output desired data.

[Sawyer05]

Apologies,
Yes Students can only enrol on one course.

Couple of questions.
Do i link the courseID (student table) to the coursenum (courses table)?
How do i implement the Taster into it?

I forgot to mention that the set of tasters will happen 3 times a year with no more than 10 students in a taster (the independant taster can have 3 groups of 10 because of the numbers while the other two only have one group).

Would it be accurate to generate a query with the criteria of each type of link to determine the groups or is it better to create a taster table?

[June7]

Examine this sample db for possible ideas.

[snoopy]

He,

why don't use a MySQL / PHP Environment, which could deal with different databases that you just linking?