For John Vinson

[Guest]

Hi,

In December you had helped me incredibly with my database. Since then, due
to an operation, things were put on hold, but are now back. If you wouldn't
mind, I still have questions...

I am listing my tables as we had discussed again. I think they're pretty
sound. I need to add another table called Awards. One student may receive
10 awards in a time period, while another 1 or none. The awards have all
kinds of different names - there is no standard because they may be within
the school or outside.

I would really appreciate you taking a quick look and suggestions on the
Awards table if you have a moment.

THANKS!

Student Database

**FamilyInfo Table :**
FamilyInfo
FamilyID
StudentFamilyName
MotherFirstName
MotherLastName
TelMotherHome
TelMotherWork
TelMotherCell
MotherEMail
FatherFirstName
FatherLastName
TelFatherHome
TelFatherWork
TelFatherCell
FatherEMail
Address1
Address2
City
Province
PostalCode
EmergencyContactName
EmergencyTelNo
Notes

**Students Table:**
StudentID
FamilyID
LastName
FirstName
DOB
Gender
SchoolGrade
NameOfSchool
Notes

**CourseInfo**
CourseID
CourseName
MonthlyRate
SpecialRate


**CourseDetailsPerStudent**
StudentID
CourseID
RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate
InitialPaymentMade (Y/N)

**ParentMeetings**
FamilyID
StudentID
DateOfMeeting
ParentAttendee1
ParentAttendee2
Student1Present
Student2Present
Student3Present
Student4Present
Notes

**StudentNotesAndReports**
StudentID
ReportType
ReportDate
Notes

**ReportTypes**
ReportTypeID
ReportType

**PaymentInfo**
PaymentID
FamilyID
Mode
Date
Amount
ChequeNo

**PaymentAllocation**
PaymentID
DepositID
CourseID
StudentID
Amount

**DepositInfo**
DepositID
DepositNumber
DepositDate

 

[PC Datasheet]

TblAward
AwardID
StudentID
<<Whatever Fields You Need For an Award>>

 

[John Vinson]

In December you had helped me incredibly with my database. Since then, due
to an operation, things were put on hold, but are now back. If you wouldn't
mind, I still have questions...

Hope you're recovering well!

I am listing my tables as we had discussed again. I think they're pretty
sound. I need to add another table called Awards. One student may receive
10 awards in a time period, while another 1 or none. The awards have all
kinds of different names - there is no standard because they may be within
the school or outside.

You need *two* tables:

Awards
AwardID Autonumber Primary Key
AwardName
<perhaps other fields such as awarding organization, memo field for
description, etc.>

StudentAwards
StudentID <link to Students>
AwardID <link to Awards>
AwardDate Date/Time
<other fields, e.g. currency field for any financial awards, notes,
etc.>

I would really appreciate you taking a quick look and suggestions on the
Awards table if you have a moment.

I'm leaving for the Easter weekend first thing tomorrow; I'll review
your table designs in detail but I won't be able to get back to you
until mid or late next week. At first glance it's looking pretty good!


John W. Vinson[MVP]

 

[Guest]

You, sir, are a saint!

I look forward to hearing from you!

Thanks for the Awards table advice!

Dee

 

[John Vinson]

I would really appreciate you taking a quick look and suggestions on the
Awards table if you have a moment.

Answers inline. These are *suggestions* not requirements. As a whole
the design looks good; some of these suggestions may (depending on
your actual needs) make it a bit better, is all.

One issue you haven't presented is the Primary Keys of your tables and
your table relationships. I'll suggest Primary Keys by putting an *
before the field or fields which would make suitable PK's.

THANKS!

Student Database

**FamilyInfo Table :**
FamilyInfo ' Is this a field? or the table name? If a field, what's in it?
*FamilyID
StudentFamilyName
MotherFirstName
MotherLastName
TelMotherHome
TelMotherWork
TelMotherCell
MotherEMail
FatherFirstName
FatherLastName
TelFatherHome
TelFatherWork
TelFatherCell
FatherEMail
Address1
Address2
City
Province
PostalCode
EmergencyContactName
EmergencyTelNo
Notes

Having father and mother fields is, strictly speaking, not normalized;
each person is an Entity. It may also (depending on the social norms
where you live) not reflect today's family realities, with divorces,
court-appointed guardians acting in loco parentis, adoption, foster
homes, and so on and so on. One possible solution would be to have a
People table in which every person (parent, student, guardian, etc.)
has an entry; this would contain PersonID, lastname, firstname, DOB,
gender, other bio information as needed. It would be related one to
one to a modified Students table which would have PersonID as its
primary key, and only student related information such as grade (no
bio information). This might be overkill though!

I'm a bit more of the opinion that you may want to split out all these
phone fields into a Phones table. This might have:

* PhoneID
Phone
FamilyID
PhoneType <e.g. Cell, Home, Work>
ContactName <for work phones particularly>
Emergency Yes/No <Yes if this is the preferred emergency number>

This could be displayed on a continuous Subform on the Family form; if
you sort by Emergency, the emergency phone will sort to the top.

**Students Table:**
StudentID
FamilyID
LastName
FirstName
DOB
Gender
SchoolGrade
NameOfSchool
Notes

**CourseInfo**
CourseID
CourseName
MonthlyRate
SpecialRate

You may have answered this but - if a given course is given repeatedly
over time, or if there are three teachers all teaching English at the
same time, will each section have its own record in CourseInfo? Or
might you need a one to many from Courses to Sections?

**CourseDetailsPerStudent**
StudentID
CourseID
RegistrationDate
RegistrationFee
TuitionFee
DiscontinuedDate
VacationDate
InitialPaymentMade (Y/N)

looks good.

**ParentMeetings** *MeetingID
FamilyID
StudentID
DateOfMeeting
ParentAttendee1
ParentAttendee2
Student1Present
Student2Present
Student3Present
Student4Present
Notes

I'm queasy about the attendees list. I can imagine a parent meeting to
discuss some interaction between, say, three students - you might have
all three students' parents present. You don't have a People table
containing the parents, as noted above; if you did, you could have the
ParentMeetings table related one to many to an Attendees table:

ParentMeetings
*MeetingID
DateOfMeeting
Notes

Attendees
*MeetingID
*PersonID

**StudentNotesAndReports** *NoteID
StudentID
ReportType
ReportDate
Notes

Maybe add a Hyperlink field to external documents such as Word files

**ReportTypes**
ReportTypeID
ReportType

**PaymentInfo**
PaymentID
FamilyID
Mode
Date
Amount
ChequeNo

**PaymentAllocation**
PaymentID
DepositID
CourseID
StudentID
Amount

**DepositInfo**
DepositID
DepositNumber
DepositDate

And these we've discussed. Looks good.

John W. Vinson[MVP]