1 of 1 people found this helpful
There are quite a number of relationships between these groups of data.
One teacher can teach many subjects
One subject can be taught by many teachers
Each class has only one teacher
One teacher can teach many classes
One class may have several hours (in a week)
At one particular day and hour, there may be several classes
A class is about one subject
One subject may be taught in many classes
One teacher can have many absences
One absence can affect many classes
So a many to many between Teachers and Subjects
A one to many between Teachers and Classes
A many to many between classes and class hours
And a join back between Subjects and Classes
Each many to many join has a joining Table with two foreign keys.
So Classes would join to teachers TeacherID to TeacherId with the Primary key being in the Teachers File.
Teachers would join to the Joining file (Teachers_Subjects) on the teachers side by TeacherId and on the Teachers_Subjects table also
Teachers_Subjects and Subjects table would also have a SubjectId to link each other
Same thing with Classes, there would need to be a joining table called say ClassHours_Classes linked together by primary and foreign keys.
Then an absence table for the teachers this could be a many to many in between Teachers and Classes containing dates teachers are absent the link could be the date in classes and the date the teacher is absent.
As far as populating the repeating events this could be scripted
Not an easy first project:)
Hi, many thanks for this. I am going to make a start on this. Will no doubt encounter lots of difficulties along the way but thank you very much for your advice. Much appreciated and very helpful.
Glad it was useful, it is often visually easier if you draw out the relationships as an ERD (entity relationship diagram) before you start, joining up all the tables and getting your head round how the data interacts.
Good luck with the project:)
1 of 1 people found this helpful
user5178 last comment is an excellent idea.
The best way to start building even the simplest database is to draw a diagram. Although rather than building an ERD diagram first which can be a little challenging for someone not working as a developer I would suggest drawing an IA (Information Architecture) diagram. This is a simple diagram that allow you to visualise the interfaces (Layouts) you need and how they link to the data. I have attached an example of one to help you get started (It is was done quite quickly so please excuse the likely poor grammar).
The is a little to small to read so I have put a full size copy up here http://weknowdata.net/school_ia.png
Once you have refined that, you can then more easily work out how many tables and the structure you will need, in the diagram attached i would say there are roughly;
4 Main Table
- Interface (to run the dashboard)
4 Join Table (These contain little data/fields, they mainly contain secondary keys to make links between table)
- Absences (Contains keys and date from, date to and may a reason)
- Lessons (Contains keys and a link between the teacher and lesson)
- TeacherToClass (Contains keys and a link between the teacher and class)
- TeacherToSubject (Contains keys and a link between the teacher and subject)
You would probably need a couple more join tables but I hope this helps to get you started.
Finally try to define small tasks within the project. By making a list of tasks the project will seem much less overwhelming. For example you can allocate yourself a task per day to fit in around your day job.
Good luck, you'll be a Filemaker Guru in no time!
We Know Data
Many thanks Rob,
I have been overwhelmed by the responses from you and user5178. You have both taken time to help me understand the task I am trying to accomplish more clearly. I have no doubt I will have questions along the way but you have given me a more structured starting point.
I have been thinking about my plans for using filemaker to manage teacher absences and allocation of class cover. I have attached a file which outlines what I want to do. I would be very grateful for any advice on what to do next. I am struggling with this to be honest and might need to call in someone who knows what they're doing to help me out. If anyone has any suggestions for how I could proceed, please let me know.
Can't read it. What's an XPS file?
XPS is a Microsoft specific document format based on XML AFAIK, andrewmcc can you convert to PDF so that us ordinary mortals can have a look? I'm editing this from Linux Ubuntu and I'm pretty sure lots of others use MACS so a universal format is always best.
Sorry! Here is the file as a PDF. Hope it makes SOME sense. I know I have missed some big relationship links but this should at least give you an idea of what I want to achieve with it.
Thanks again for looking at it.
This is the aforementioned document in PDF.
Forgive me for stating the obvious but isn't this project gasping for an experienced developer?
There is always the possibility of re-inventing the wheel so before you get too deep I would suggest having a look at what these guys are doing: http://www.cjmsoftware.co.uk/index.html I have no connection bu they appear to be in the same area.
The project is aplty named since, in order to create an absentee record, it is necessary to have an effective table of arrangements made for teachers, classes and rooms aka A Timetable.
classes - teachers teach (a class is a collection of pupils with an allocated name)
subjects - each teacher can teach specific classes/material
rooms - in which the teaching takes place
period - in this case there are 6 peiods per day
absence is created when a teacher is no longer able to teach an allocated class
The relationships between these entities are broadly :
teacher:subjects 1 to many
teacher:room 1 to many (time conditional)
teacher:period 1 to many
teacher:absence 1 to many
teacher: class 1 to many
If you create a hand drawn ERD showing these relationships then you can run through the various connections to see if the dependencies make sense. When they do make sense then you can start creating tables and fields within the Graphical relationship Graph in FMP.
I'm UK based and I'd be happy to help and can be reached at email@example.com. .
Not unusually, some information necessary to make the system somewhat easier to develop has not been provided.
The naming standard for a class of pupils is not clear in that Period 1 can have three classes with names such as 3M Geo, 2B3 Eng and 5E2 Art which does not identify the specific classroom needs but these names may contain information about streaming or the 'year' that the children who make up the class belong to.
Furthermore, the categories P, L, S and I are not explained. It would be most useful to be able to reproduce at least a week of allocated classes with classrooms, teachers, class names, subjects and period. A sanctioned absence request by a teacher would then produce a vacancy which would need to be filled from the remaining teacher 'float'. Each teacher in the float would need to be flagged as having the capacity to teach the vacant class subject and level. All teachers would start off by being in the 'float' until they were allocated a 'slot'. And so on....