Database relations

[Viktor via AccessMonster.com]

Hi,

I guess this is pretty elementary situation but for some reason I'm stuck. I
have the following situation - I need to make a database, one part of which
would be used for tracking the courses visited by different students and
which would sum the grades and the amount paid for different courses so they
could get a discount if they take more courses in future. Also, I'd like to
have a list displaying the name of the course, the start date and the
students in that group.

Ihave the following tables

Courses
------------
crsID - autonumber
crsName - text
crsDesvription - text
crsNumberLessons - number
crsPrice - number

Students
------------
stdID - auto
stdName
...

CoursesPaid
--------------
cpdID - auto
cpdStudentID - number
cpdTotalCourses - number
cpdTotalSum - number

CoursesTaken
-------------------
ctkID
ctkCourseID
ctkStudentID
ctkTecherID
ctkAmountPaid - (amount paid so far, if the student pays on a monthly bases)
ctkPaid - yes/no (it should be yes if the amount paid equals the crsPrice in
the courses table)
ctkStartDate

Courses ----1:m--->CoursesTaken <----m:1----- Students <----1:1 ----> Paid

Is this the optimal database disign and could I achieve everything I mention
with this database design?

Thanks

 

[Tim Ferguson]

sum the grades and the amount paid for different courses

Does this mean simply maintaining a "running total" of amounts paid
(which would be an attribute of the Student) or a history of the payments
for each course (in which case it would be an attribute of CoursesTaken)?

I am assuming that you can't calculate the total paid from the Courses
table if the fee varies over time, because a query like this would
reflect current prices rather than those prevailing at the time.

select sum(Price) from courses
where courseID in
( select courseID from coursesTaken
where studentID = 1856754
)



Hope that helps


Tim F