Proper Relationships Design (1 Viewer)

[HanneSThEGreaT]

Hello everyone!

I'm a noob with designing databases, I've always tried to avoid programs which includes databases, but heck, it's seems my luck has run out

OK, this is what I have in my Access 2000 db, and what I'd like to acieve.

Tables
StudentInfo
CourseInfo
SubjectInfo

Fields in these tables:
StudentInfo

StudentNo - Primary Key
FileNo
TimeSlot
Class
StartDate
FullNames
Surname
Course
Monthly
NoPayments
CourseID - Primary Key
StuRecNum

CourseInfo
CourseID - Primary Key
CourseName

SubjectInfo
CourseID - Primary Key
SubjectName - Primary Key
StartDate
EndDate

As the design currently is it will end up giving me this details (if I have put values in)
For example:
Record1
FullNames - Hannes
Surname - du Preez
StudentNo - 123
CourseID - 3
CourseName - Getting Started
StartDate - 03/10/06
EndDate - 27/10/06

Record 2
FullNames - James
Surname - Bond
StudentNo - 456
CourseID - 3
CourseName - Getting Started
StartDate - 03/10/06
EndDate - 27/10/06

The problem here is that, based on the "old" design, it inputs the same StartDates and EndDates for the particular course (which makes sense, and what they initially wanted) - Now they want to be able to have different StartDates and EndDates for each student, something like:

Record1
FullNames - Hannes
Surname - du Preez
StudentNo - 123
CourseID - 3
CourseName - Getting Started
StartDate - 03/10/06
EndDate - 27/10/06

Record 2
FullNames - James
Surname - Bond
StudentNo - 456
CourseID - 3
CourseName - Getting Started
StartDate - 09/10/06
EndDate - 03/11/06

Can it be done ¿ How ¿

I'll be greatful if anyone can help me out
Thanx!

 

[Steve R.]

Tables
StudentInfo
CourseInfo
SubjectInfo

Fields in these tables:
StudentInfo

StudentNo - Primary Key [B]<=Do NOT use as the student number. Use only to link tables as a "hidden value".[/B]
FileNo
TimeSlot
Class
StartDate
FullNames
Surname
Course [B]<=Delete, unless you intend to use as a major.[/B]
Monthly [B]<= Implies the need for an accounting table[/B]
NoPayments [B]<= Implies the need for an accounting table[/B]
CourseID - Primary Key [B]<=Change to Foreign KEY from CourseInfo[/B]
StuRecNum [B]<=Why StuRecNum, StudentNo, and FileNo?[/B]

CourseInfo
[B][U]CourseInfoIDnum - Primary Key[/U][/B]
CourseID - Primary Key [B]Change to an integer.[/B]
CourseName
[B][U]CourseCost[/U][/B]
[B][U]CStartDate[/U][/B]
[B][U]CEndDate[/U][/B]
[B][U]CMemo[/U][/B] 

SubjectInfo[B]<=Delete[/B]
CourseID - Primary Key[B]<=Delete[/B]
SubjectName - Primary Key[B]<=Delete[/B]
StartDate[B]<=Delete[/B]
EndDate [B]<=Delete[/B]

One issue not resolved is what happens should the student be taking more than one course? This can be solved by a table that has the studentid associated with each courseid.

 

[HanneSThEGreaT]

Thanx for the info, you're too kind for words.
Just a note about the payments and monthly columns they will be used with another table (for payments) - but at this stage I'm far waya from that.

I want to enter a studentnumber, based on that studentnumber, I want his / her course along with the course's subjects and respective start dates and end dates.

I was almost finished with this app, when the ball game changed - I'm really not a database expert, for all I'm concerned I'll put everything in one table and get it over and done with - but, the problem is normalisation.

I've atttached my db ,as it is currently
Any advice ¿