4 Replies Latest reply on May 22, 2017 1:48 AM by appt

    Different accounts for different teachers at different schools

    appt

      Evening all,

       

      I've been trying for a couple of days now to set up a database as a test that will allow teachers from different schools to access records only for students from their school.

       

      I need each teacher to login and be able to create classes and add students to those classes.

       

      As admin I will assign school names and account names.

       

      Next time they log in they should only be able to select their own classes form a dropdown and go to those classes.

       

      I have set up 3 tables Students - Classes - Schools with each school able to have many classes and each class able to have many students.

       

      I've used privilege sets but it greys out the new record option even though I have selected the ability to create records. I'm missing something and maybe it's very complex but any help would be appreciated.

        • 1. Re: Different accounts for different teachers at different schools
          fmpdude

          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.


          Below,

           

          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

          (

          SCH_ID INTEGER,

          `School Name` CHAR,

          SCH_Name CHAR

          );

           

          CREATE TABLE COURSE

          (

          COURSE_ID INTEGER NOT NULL AUTO_INCREMENT,

          COURSE_NAME VARCHAR(255),

          id INTEGER,

          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,

          STUDENT_NAME VARCHAR(20),

          PRIMARY KEY (STUDENT_ID)

          ) ENGINE=InnoDB;

           

          CREATE TABLE STUDENTCOURSE

          (

          STUDENT_ID INT,

          COURSE_ID INTEGER,

          COURSE_SCHOOL_FK INTEGER UNIQUE

          ) ENGINE=InnoDB;

           

          CREATE TABLE COURSE_SCHOOL

          (

          id INTEGER,

          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);

          • 2. Re: Different accounts for different teachers at different schools
            philmodjunk

            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.

            • 3. Re: Different accounts for different teachers at different schools
              appt

              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.

              • 4. Re: Different accounts for different teachers at different schools
                appt

                Hi Phil,

                 

                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.