How would you set up this database?

[razgriz_110]

I'll give you the problem first:

I have 50 "tutors" who are available at different times on different days of the week to teach certain classes. What would be the best possible way to arrange the tables/fields on MS access so that I know exactly which tutors are available for a 1-hour session on any given day? Furthermore, each class must always have at least 3 tutors for the session to run. Therefore, I can not schedule a session for subject X on, say, Tuesday at 1400h if only 2 tutors are available for that time and day.

As you can see, there are a few problems which need to be addressed:

1) The actual structure of the database: How can I set up the fields to best retain the schedules of each tutor? Right now, I'm thinking for each tutor, I have 5x11 = 55 boolean fields (For each of the 5 days, the availability or any given tutor from 0800h to 1800h, broken down into 11 1-hour slots, as I don't plan on holding sessions past 1800h).



2) Once I have all their schedules entered into a database, how can I generate a report where it'll show me exactly which tutors are available for each 1-hour slot (between 0800h and 1800h) across the 5 days? I need to do this so that I can quickly see which tutors, if any, are available at XXXXh on a certain day, since at least 3 tutors must be available if a session is to be held at that time.

More relevant info: there are 5 different subject (Math, English, Biology, Chemistry, Physics), which means that there are roughly 10 tutors who are qualified to teach each subject (though some tutors are qualified to teach multiple). Again, I'm thinking using boolean fields - one for each subject - for each tutor. A "yes" would indicate that that tutor is qualified to teach subject X. The reason for doing this is so that I can use a query to isolate by subject X, and THEN generate a report with the 10 or so timetables overlapped.

P.S. This is going to be a system where at the beginning of every semester, the tutors (who are actually senior university students) submit their schedules to me, and after it is entered into the database, I need to be able to "overlap" their schedules in a report to quickly check for possible class timings for subject X.

[jzwp11]

Welcome to the forum!


Using a bunch of boolean fields is not the correct way of handling it in Access (it might be fine for Excel but not Access). You would end up with a non-normalized database which will be difficult to work with over the long run. For more on normalization, please check out this site.

For example, you mentioned that not all tutors can teach all subjects but some tutors can teach multiple subjects. A subject can be taught by many tutors: this is a one-to-many relationship. A tutor can teach many subjects: another one-to-many relationship. When you have 2 one-to-many relationships between the same 2 entities (subjects and tutors in this case) you have what is called a many-to-many relationship which is handled with a junction table.

First a table to hold the tutor info

tblTutors (1 record for each student)
-pkTutorID primary key field, autonumber datatype
-txtFName text field to hold tutor's first name
-txtLName text field to hold tutor's last name

Then a table to hold the subjects

tblSubjects (1 record for each subject)
-pkSubjectID primary key, autonumber datatype
-txtSubjectName text field to hold the subject name

Now the junction table to associate the tutors and the subjects for which they are qualified to teach

tblTutorSubjects
-pkTutorSubjectID primary key, autonumber datatype
-fkTutorID foreign key field to tblTutors, long number datatype
-fkStudentID foreign key field to tblSubjects, long number datatype

You would use a similar approach to the days of the week and the 1 hour time slots. A time slot can occur on multiple days and a day can have multiple 1 hour time slots. So you will need 3 more tables

tblDays (assuming 5 days on which tutoring will occur, so 5 records)
-pkDayID primary key, autonumber
-longDayNumber long number field to hold the day number, for example you may assign 1 for Monday, 2 for Tuesday etc

tblTimeSlots (1 record for each of the 11 one hour time slots you mentioned)
-pkTimeSlotID primary key, autonumber
-tmeStart


Now for each day you will have the 11 slots, so you need to bring those together in a junction table (5x11=55 records)

tblDayTimeSlots
-pkDayTimeSlotID primary key, autonumber
-fkDayID foreign key to tblDays, long number datatype
-fkTimeSlotID foreign key to tblTimeSlots, long number datatype

Now, a tutor can have many available day-time slots and a day-time slot might have many tutors associated with it. This again describes a many-to-many relationship, so another junction table is needed

tblTutorDayTimeSlots
-pkTutorDayTimeSlotID primary key, autonumber
-fkTutorID foreign key field to tblTutors, long number datatype
-fkDayTimeSlotID foreign key field to tblDayTimeSlots, long number datatype

I assume that you will also need a series of table (similar to the above) where you define the day-time slots for the various subjects. And then, of course, you will have to assign students to those subject-day-time slots based on their availability which you defined with the tables above.

[bobeboston]

Hi, I greet you all and thank you for all the help you have been giving people like us.
My name is Stanley, and i am just a beginner in access trying to upgrade my skills. I have a project i want to bring up, but i don't know how to start it up. Please read carefully and try to help me out.

I am in Cameroon and i need to design a school database for a Secondary School with about 2000 students. This database should have features as follows:
* Keep a student profile and their guardian,
* Keep record of each school fee transaction,
* Records the test marks of students and at the end produce an automatic report card sheet,
* Generate class council report sheets, master sheets, at the end of each academic year,
* Generate each students transcripts when demanded,
* Automatically promote students to next class, if the average is >=10
* Keep track of school teachers (employee),

The FIRST problem i face is organizing or structuring the tables. Each class in the school has sub classes E.g Form 1 has A, B, and C, likewise Form 2 = 2A, 2B, 2C; From 3 - 5 the same. Each class has about 11 subjects maximum, but not all students take all the subjects especially at higher classes as Form 4 and Form 5. I also want to take note of academic sessions. I want it to automatically promote a student at the end of each academic session to the next session and in a new class. E.g. A child just newly admitted into Form 1A, this session 2011/2012 should be automatically moved to say Form 2B in September 2012 when the academic session 2012/2013 starts.

Please, if anyone knows how to structure this database, let the person help me. Thank you very much. I really need to upgrade my little skills in database design.

[jzwp11]

Welcome to the forum!

The most import thing about starting a database is getting your tables and relationships set up correctly. Forms and reports have to wait until the tables and relationships are set up. In setting up those tables and relationships, it is important to follow the rules of normalization. As I mentioned in my initial post in this thread, please check out this site for more on normalization.

You might find the tutorials on this site helpful both in table/relationship design but also on form and report design as well.

Just some other general recommendations:
1. Do not use spaces or special characters in your tables and field names
2. Do not use look up fields at the table level. (See this site for reasons why it is not a good idea).
3. Be careful not to use reserved words as table or field names. Here is the list of reserved words.

I generally recommend having an autonumber, primary key field in every table. This allows Access to assign a unique identifier to each record in a table. The primary key field will join with its respective foreign key field in another table to form the relationship. All foreign key fields should be long number data types to match the autonumber primary key field.

Now as to your database, you have to analyze the data you intend to store in the database and group similar data together. Similar data is held in a table. Then you have to determine how those groups of similar data are related to one another.

You mention students, guardians and teachers. These are all people, thus, their basic information should be stored in 1 table

tblPeople
-pkPeopleID primary key, autonumber
-txtFName text field to hold person's first name (given name)
-txtLName text field to hold person's last name (surname)

If a person (student) is related to another person (guardian), you would need a table to bring that information together.

tblRelatedPeople
-pkRelatedPeopleID primary key, autonumber
-fkPPeopleID foreign key to tblPeople, long number datatype field
-fkSPeopleID foreign key to tblPeople, long number datatype field

Both foreign key fields above refer back to tblPeople, but you cannot have more than one field with the same name, so I distinguished them using the letters P an S shown in bold.

What type of profile information do you intend to keep on each person?

Whay type of fee transactions do you plan to keep?

Can a student have many fees?

Will many students be responsible for paying the same type of fee?

Each class in the school has sub classes E.g Form 1 has A, B, and C, likewise Form 2 = 2A, 2B, 2C; From 3 - 5 the same.

What do you define as a class?

What distinguishes a class from a subclass?

How do students move from one subclass to another and/or from one class to another?

How are the students assigned to classes/subclasses?

I assume students will take many classes and a class can be taken by many students.

Are certain classes associated with certain academic sessions?

I assume that a student can be with you for many academic sessions.

I assume that students may take many tests while in a subject.

Is there a relationship between the subject and the class/subclasses?

Generate class council report sheets, master sheets, at the end of each academic year

The above sound like reports. As I wrote earlier, reports must wait until later, but at this point we do need to understand what type of data is contained within the report so that we capture that in our table structure. So could you provide more detail on what type of data is contained in these reports?

[bobeboston]

Hi JZWP11,
thanks for the reply. Let me clarify somethings here where you had doubts.

1. What i meant by class is actually "Classrooms". In African secondary schools, due to population, we usually have multiple classrooms belonging to same level of studies. For example, A student attending a secondary school for the first time is admitted into Form 1. And this Form 1, could have classrooms Form 1A, Form 1B, Form 1C etc depending on the number of students admitted. Likewise, a student in Form 2 (2nd year student) may belong to either F2A, F2B, F2C etc.

2. What distinguishes the classrooms is the letters A, B, C or D. A student doesn't need to move from an A classroom to B classroom of the same level of education. He/she might change from A to B if and only if the other classrooms in the next level of education are taken. For example, a second year student in a C classroom (Form 2C) may be promoted to Form 3A (3rd year) depending on the arrangement of the school administration. They may decide to start selecting students for the A classroom according to the students performance in the previous (2nd year) level.

3. At each academic year, a student must belong to one and only one sub-classroom (either A, B, c oar D).

4. At evry level, each classroom/sub-classroom has the same subjects (courses) and the children may offer them all.

5. An academic year is divided into 3 TERMS (1st, 2nd and 3rd terms). In each term a student takes 2 Tests called (1st Sequence and 2 Sequence). 2nd Term has 2 tests (3rd Sequence and 4th Sequence); 3rd Term has 2 Tests (5th Sequence and 6th Sequence) and all the students are obliged to take the tests.

6. About the school fee transactions, a student might decide to pay the fee in installments. So, it should be tracked. The fields here could be; Tuition, PTA levy, Registration etc.

7. The masters sheets, Class council sheets, and transcripts are REPORTS. That we treat later. For now, i want to concentrate on the TABLE designs.

hi everyone, i hope my explanation is clear now. thank you very much for the help so far. I look forward to being of help too soon for someone else.

[jzwp11]

Thank you for the further explanation.

You will need a table to hold the various levels of studies with records Form 1, Form 2 etc. You will also need a table to hold the classrooms as records A, B, C etc. So something like this

tblLevelsOfStudies
-pkLevelID primary key, autonumber
-txtLevel

tblClassrooms
-pkClassroomID primary key, autonumber
-txtClassroom

Then you will need to associate the various classrooms to their level of studies. This will require a junction table

tblLevelClassrooms
-pkLevelClassroomID primary key, autonumber
-fkLevelID foreign key to tblLevelsOfStudies
-fkClassroomID foreign key to tblClassrooms

Since each academic year always has the same 3 terms you again would need junction table

tblAcademicYear
-pkAcademicYearID primary key, autonumber
-longAcademicYear (long number field to hold the year)

tblTerms
-pkTermID primary key, autonumber
-Term (either a long number field to hold 1, 2, 3 as records or a text field if you prefer 1st, 2nd, 3rd.)

tblAcademicYearTerms
-pkAcadYearTermID primary key, autonumber
-fkAcademicYearID foreign key to tblAcademicYears
-fkTermID foreign key to tblTerms

5. An academic year is divided into 3 TERMS (1st, 2nd and 3rd terms). In each term a student takes 2 Tests called (1st Sequence and 2 Sequence). 2nd Term has 2 tests (3rd Sequence and 4th Sequence); 3rd Term has 2 Tests (5th Sequence and 6th Sequence) and all the students are obliged to take the tests.

Are the tests associated with the subjects being taken or do the tests cover all subjects? Are the tests geared toward the level of study for which the student is being tested?

The fields here could be; Tuition, PTA levy, Registration etc.

No, the different fees should be records in a table not fields

tblFees
-pkFeeID primary key, autonumber
-txtFeeName

As to the amount of the fees, do they change from term to term or from year to year? If so then that says that a fee can have many amounts (over time) and thus describes a one-to-many relationship which requires a separate but related table.

You would then associate the fees to each student.

You would also need a payment table so that you can track the installment payments for the fees.

4. At evry level, each classroom/sub-classroom has the same subjects (courses) and the children may offer them all.

I'm a little unclear on this. Are you saying that the subject taught are associated with the level of study? Can you provide a couple examples?

[bobeboston]

Thanks jzwp11. If everyone was like you, then no need for one to enter a four-walled-classroom.

---Quote---
The fields here could be; Tuition, PTA levy, Registration etc.
---End Quote---

In this case, let me give you an example. The school administration may decide that this year 2011/2012 academic year, a student pays $10,000 as total school fees divided as; Tuition = $5000, PTA levy = $4000, and Registration = $1000. So, it is up to the student to decide how he pays it. A student who is not reach may decide to pay the 10,000 in bits. This month he may pay $2000, next month he pays $5000 etc but the money should be finished before end of the session.

Next academic session 2012/2013, the school administration may or may not decide to change to total school fees. So, i will say it's not sure if it should be a constant or not. So, how do we relate that information?

---Quote---
4. At evry level, each classroom/sub-classroom has the same subjects (courses) and the children may offer them all.
---End Quote---
I'm a little unclear on this. Are you saying that the subject taught are associated with the level of study? Can you provide a couple examples?

Here, i will use an example. Let's take for example, Form2 (that is 2nd year students). Assuming it has 3 classrooms - 2A, 2B and 2C; each of these classrooms will have as subjects,
Maths, English, French, Biology, Physics, Chemistry, History, Geography, Economics, Food & Nutrition and Sports for the students to choose from.

As you go higher to other levels, they may have additional subjects added to their coursework. For example, Further Mathematics, Human Biology, Geology may be added. So, generally, there are some basic subjects to be taught, that are common to every level of studies. So, are we going to make a table of subjects for every classroom at each level?

Quote:
4. At evry level, each classroom/sub-classroom has the same subjects (courses) and the children may offer them all.
I'm a little unclear on this. Are you saying that the subject taught are associated with the level of study? Can you provide a couple examples?

About the tests, at each level of study, each subject taught will have 2 evaluation tests per term. In order words, in this academic year 2011/2012 for a 2nd year student (be it in 2A, 2B or 2C), he/she is expected to take 2 tests (called 1st Sequence, and 2nd Sequence) for every subject taught. That is, two tests in Maths, 2 tests in English, 2 tests in French etc. Likewise for those in other Levels.
I don't know if that is clear enough.

[jzwp11]

In this case, let me give you an example. The school administration may decide that this year 2011/2012 academic year, a student pays $10,000 as total school fees divided as; Tuition = $5000, PTA levy = $4000, and Registration = $1000. So, it is up to the student to decide how he pays it. A student who is not reach may decide to pay the 10,000 in bits. This month he may pay $2000, next month he pays $5000 etc but the money should be finished before end of the session.

Next academic session 2012/2013, the school administration may or may not decide to change to total school fees. So, i will say it's not sure if it should be a constant or not. So, how do we relate that information?

Since it is likely that the fees can change from one academic year to the next, we will need to join the fee amounts to the academic year. Based on the previous discussion, we had these two tables:

tblAcademicYear
-pkAcademicYearID primary key, autonumber
-longAcademicYear (long number field to hold the year)

tblFees (3 records: tuition, PTA levy, registration)
-pkFeeID primary key, autonumber
-txtFeeName

Now relate the two tables above in a third table and bring in the actual fee amount

tblAcademicYearFees
-pkAcademicYearFeeID primary key, autonumber
-fkAcademicYearID foreign key to tblAcademicYear
-fkFeeID foreign key to tblFees
-currAmount (currency field to hold the actual fee amount)

Since it sounds like the fees are independent of the student's level or class, I do not think that we need to associate the fees themselves to the student since for an academic year, the fees for all students will be the same (correct me if I am wrong). If as you say that the fees must be paid before the end of the academic year, then we do not need to capture the academic year in the payment record since we can associate the payment date with the academic year; however, if payments can go beyond the academic year, then you would need to capture the academic year for which the payment applies (include fkAcademicYearID field in the table below).

tblStudentFeePayments
-pkStudentFeePayID primary key, autonumber
-fkPeopleID foreign key to tblPeople (represents the student)
-dtePayment (date of the payment)
-currAmtPaid (currency field to capture the amount paid)

Going back to the academic year table, since you represent the academic year as 2011/2012, we might want to adjust the longAcademicYear field to a text field

tblAcademicYear
-pkAcademicYearID primary key, autonumber
-txtAcademicYear

If you want to be specific as to start and end dates for the academic year (1 academic year has 2 dates associated with it--i.e. a one-to-many relationship), you could have the following table. I don't know if you really need this table, so at this point it would be optional.

tblAcademicYearDates
-pkAcademicYearDateID primary key, autonumber
-datefield
-txtDateType (start or end)

Some people would just include the start and end dates to the academic year as two separate fields in tblAcademicYear, but technically speaking having the related table would be the more normalized approach. I'll leave it up to you.

As to the subjects, you would have a table that holds all subjects.

tblSubjects
-pkSubjectID primary key, autonumber
-txtSubject

Now if you really want, you can associate the subjects to the level in which they are taught

tblLevelSubjects
-pkLevelSubjectID primary key, autonumber
-fkLevelID foreign key to tblLevelsOfStudies
-fkSubjectID foreign key to tblSubjects

We also have to associate the student in all of this for which the logic is still a little fuzzy to me, but I'll give it a try. Please correct me if you think I am going down the wrong path.

Within each academic year, you will have many levels

tblAcademicYearLevels
-pkAcademicYearLevelID primary key, autonumber
-fkAcademicYearID foreign key to tblAcademicYear
-fkLevelID foreign key to tblLevels

For each level in an academic year, you will have many students. Each student will stay at that level for the entire academic year (correct?)

tblAcademicYearLevelStudents
-pkAcadYearLevelStudentID primary key, autonumber
-fkAcademicYearLevelID foreign key to tblAcademicYearLevels
-fkPeopleID foreign key to tblPeople (representing the student)

Assuming that the list of subjects changes from term to term within an academic year, I think this table is in order. I also think that this is where we need to capture the classroom since a student might change classrooms when they change terms. If a student can change classrooms within a term then this structure would not be correct:

tblAcademicYearLevelStudentTerm
-pkAcadYearLevelStudentTermID primary key, autonumber
-fkAcadYearLevelStudentID foreign key to tblAcademicYearLevelStudents
-fkTermID foreign key to tblTerms
-fkClassroomID foreign key to tblClassrooms

For each term, a student will have many subjects

tblAcademicYearLevelStudentTermSubjects
-pkAcadYearLevelStudentTermSubjectID primary key, autonumber
-fkAcadYearLevelStudentTermID foreign key to tblAcademicYearLevelStudentTerm
-fkSubjectID foreign key to tblSubjects

For each subject within a term, each student will have many test scores (2 as you describe)

tblAcademicYearLevelStudentTermSubjectsTestScores
-pkAcadYearLevelStudentTermSubjectScoreID primary key, autonumber
-fkAcadYearLevelStudentTermSubjectID foreign key to tblAcademicYearLevelStudentTermSubjects
-testscore
-dteTest (test date)

Feel free to shorten/rename the tables above; I use the long names so that you can see the logic as I step through the tables and relationships.

Looking back, you may not need this table since it really doesn't do anything for us.

tblAcademicYearTerms
-pkAcadYearTermID primary key, autonumber
-fkAcademicYearID foreign key to tblAcademicYears
-fkTermID foreign key to tblTerms