Well, I'm not sure about your modeling and I would recommend you get a real ERD software program like SQL Editor for the Mac.
Does this ERD (using SQL Editor) below look like it might work? I can't be totally sure from your description, but the database below would enable you to filter courses from a particular school (via the FK in the course table). From there, you could add students to the STUDENT_COURSE table.
1. a school can have many courses and a course can be in many schools, M:M.
2. A student can be in many courses and a course can hold many students, M:M
3. You should be able to only add courses for a particular school (or enforce that in the logic).
This is just a quick diagram so I'd have to play with it with some actual data and refine it based on your replies.
*** CLICK ON THE DIAGRAM IF IT DOES NOT FULLY SHOW UP ON YOUR DISPLAY ****
It would be helpful if you post some data for the tables you have with your desired query results.
BTW, if this is helpful, here's the DDL for MySQL as a SQL file. It's easy to create a database in MySQL from an SQL file.
(SQL Editor creates all this automatically and would even create the MySQL or FMP database (and others, like Oracle) itself directly from the model above.)
/* SQLEditor (MySQL (2))*/
CREATE TABLE SCHOOL
`School Name` CHAR,
CREATE TABLE COURSE
COURSE_ID INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (COURSE_ID)
) ENGINE=InnoDB COMMENT='A teacher can only add classes and should only see the course';
CREATE TABLE STUDENT
STUDENT_ID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (STUDENT_ID)
CREATE TABLE STUDENTCOURSE
COURSE_SCHOOL_FK INTEGER UNIQUE
CREATE TABLE COURSE_SCHOOL
SCH_ID INTEGER UNIQUE,
COURSE_FK INTEGER UNIQUE
ALTER TABLE SCHOOL ADD FOREIGN KEY SCH_ID_idxfk (SCH_ID) REFERENCES COURSE_SCHOOL (SCH_ID);
ALTER TABLE COURSE ADD FOREIGN KEY COURSE_ID_idxfk (COURSE_ID) REFERENCES COURSE_SCHOOL (COURSE_FK);
CREATE INDEX STUDENT_ID_idx ON STUDENTCOURSE (STUDENT_ID);
ALTER TABLE STUDENTCOURSE ADD FOREIGN KEY studentcourse_ibfk_1 (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX COURSE_ID_idx ON STUDENTCOURSE (COURSE_ID);
ALTER TABLE STUDENTCOURSE ADD FOREIGN KEY studentcourse_ibfk_2 (COURSE_ID) REFERENCES COURSE (COURSE_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE COURSE_SCHOOL ADD FOREIGN KEY id_idxfk (id) REFERENCES STUDENTCOURSE (COURSE_SCHOOL_FK);
Sounds like your privilege set specifies minimum menus. There's a drop down to control that in the dialog where you select options for your privilege set.
Thank you very much.
In actual fact in this model it is not necessary for a student to have many classes or many teachers.
At present I have 20 solutions running on a server, one for each school. But of course any changes to the design need to be made 20 times. I'd like one solution.
A teacher logs in at the start of the year with an account name that matches the name of the school.
They create a class (or several) and that class is linked to that user account/school.
The teacher adds students to the class.
Another teacher from another school logs in with their account name / school name.
That teacher also adds a class and students to the class.
The next time they log in and select a class for which they want to add some data they do it from a drop down list that only populates with the classes they created.
The solution works fine as an individual file but as I say there are 20 of them!
I can post a little more data shortly.
thanks for that. Yes you are right it was set to minimum. I have now set to all and will continue to work on this.