GSDatabaseSimplified (1) (PDF)

All Schools will
have a N/A
campus and all
campuses will
have an N/A class

Comments
PK

CommentID
DateTime
Comment

CommentType
PK

CommentTypeID

FK

ClassID

FK

CommentType

DaysOfTheWeek
PK

PKFK Cla

PKFK Da

DayID

Sta

Days

En

CommentType

Each Subcategory
of MCP will have
N/A option,
Though we will
likely never use it.

WardTown
PK
District
PK

MajorCityProvince
PK

CityProvinceID

WardTownID
WardTownName

DistrictID

FK

DistrictID

DistrictName
FK

CityProvinceID

CityProvinceName
Campuses
Schools
PK

PK

SchoolID

CampusName

SchoolName
FK
Qry will select email of contact from
SchoolContacts table where Campus
id and class id = N/A and contacttype
= Invoicing
Or potentially just take email from
contact based on a dropdown
selection on Invoice generation form.

FK

WardTownID

GSPrice

FK

SchoolID

LSPrice
FK

AccountManager

MainCampus is primarily used so
the school has a main address. It
will always have a maincampus

SchoolID
Address

FK

GSLSREPUnits
PK

InvoiceID
InvoiceDetails
PK

InvoiceID

FKPK MaterialsID

WardTownID

Quantity

Email

Price

MonthID
We only ever invoice schools. We only ever invoice 1 time per month (based
on MonthID). Every invoice will have every classUnitMonth record for that
school for that month. Each class will have the number of Students for that
month (qry Sum(studentid) where joindate dropdate are outside or equal to
the bounds of that month (these are licenses). Many invoices have
Materials as well. (Qry MatsID Quantity Price where schoolid matches inv
and orderdate is within month bounds.

All Schools will have a N/A campus and
all campuses will have an N/A class
ClassUnitMats
PK,FK ClassID

Possible
PK,FK MaterialsID
materials
Number
include
ClassroomMats,
OrderDate
StudentMats,
and
TeachMats

ClassesUni
PK,FK ClassID

UnitID
UnitName

Date
FK

Address

MainCampusID

Invoices
PK

CampusID

We have 2 curricula
actually have a tea
usually different tea
have a toddler cur
types total and we w
DB for each

UnitType

PK,FK UnitID

Start Date

ClsUnitMonths

End Date

PK,FK ClassID
PK,FK UnitID
PK,FK MonthID

Materials are ordered and delivered in particular months for a
particular class and unit. Sometimes they order extra materials in
subsequent months. We need Total for unitclass, and monthly #s,
Both of these will be achieved by qry for the materials in question
where the orderdate of said materials is within either the startdate
enddate or month id according to which numbers we want to pull.

Months
PK

MonthID
Month_year

It is desirable to have the units completed by a student. I toyed with the idea of making this a
table.. but that would present a weird interaction. Many to many classes to students, many to
many classes to units, many to many students to units.
The desired list of completed units is done by selecting ClassStudents.StudentID, ClassID, and
ClassesGSUnits.GSUnitID Where startdate is greater or equal to join date, AND (end date is
lesser or equal to dropdate OR dropdate is null)

ClassSchedules

KFK ClassID

Interestingly this will be how we calculate the total student number in a class and the total
materials number in a class and the teacher of a class... All of them will use join date and start
date / dropdate enddate to compare whether the time periods overlap...

KFK DayOfTheWeek
StartTime
EndTime

ClassStudents

PK

FK

PK,FK StudentID

StudentID

JoinDate

EnglishName (first only)

ClassName

DropDate

LastName

StartingStuidentNumber

VNFirstName

ClassType

DOB

PhoneNumber
PK

CampusID

PhoneID
CountryCode
DistrictCode

ClassTeachers
Teachers
PK,FK TeacherID

PK

TeacherID
EnglishName (first only)

JoinDate

LastName
VNFirstName(NullOK)

Materials
PK

TeacherType

MaterialsName

FK

SchoolContacts

MaterialType

PK,FK ContactID

UnitID

PK,FK ClassID

nitID

FK

ContactTypes

D
PK

ContactTypeID
ContactType

A school/campus/class can
have more than one contact,
one contact can be at more
than one school, one contact
can have more than one
contact type at one school or
many schools

PrimaryorSecondary

TrainersPhones

PK

TrainerID
EnglishName (first only)

All Schools will have a N/A
campus and all campuses will
have an N/A class

nd Date

PK,FK TeacherID

TrainerID
Trainers

PK,FK ContactTypeID

tart Date

TeachersPhones

PK,FK PhoneID

Email

MaterialsID

MaterialPrice

PhoneNumber

PK,FK ClassID

DropDate

assesUnits

year

PK

ClassID

curriculae, one is elementary level, these classes
ave a teacher class and listening class each with
erent teachers (same students and unit). Then we
ddler curriculum, this only has one class. 3 class
and we want to be able to keep a schedule in the
B for each, have teacher of each, etc. etc.

lassID

Students

PK,FK ClassID
Classes

PK,FK TrainerID
PK,FK PhoneID
PrimaryorSecondary

LastName
VNFirstName (Null OK)
Email

Contacts_Phone
PK,FK ContactID
PK,FK PhoneID

Contacts
PK

ContactID
FirstName
LastName
Title
Email

PrimaryorSecondary