3 Replies Latest reply on Feb 23, 2009 12:08 PM by disabled_menno

    Portal Question?

    snkm

      Title

      Portal Question?

      Post

      I have a relational database connecting one employee to several projects.  How do I also connect one project to several employees?

       

      Currently I have the following:

       

      EMPLOYEE_ID:  001  NAME:  Mr. Smith

      --------------------------------------------------

      PORTAL

      --------------------------------------------------

      PROJECT_ID:  001    DESCRIPTION:  Doing laundry

      PROJECT_ID:  002    DESCRIPTION:  Eating lunch

      PROJECT_ID:  003    DESCRIPTION:  Cleaning Dishes

      --------------------------------------------------

       

      I need to also have the following:

      PROJECT_ID:  001    DESCRIPTION:  Doing laundry

      --------------------------------------------------

      PORTAL

      --------------------------------------------------

      EMPLOYEE_ID:  001  NAME:  Mr. Smith

      EMPLOYEE_ID:  001  NAME:  Mr. Stevens

      EMPLOYEE_ID:  001  NAME:  Mrs. Anderson

       

       

        • 1. Re: Portal Question?
          hiatts
            

          Called a many to many relationship... ie 1 or more employess can work on 1 or more projects...

           

          You need to normalise the design and move to 3 tables...

           

          so you have the following tables

           

          employees

          projects

          employees on projects

           

           

          • 2. Re: Portal Question?
            snkm
              

            I do have the tables you listed below:

             

            employees

            projects

            assignments

             

            However, I don't quite understand how to connect them in the relational graph.  Also, I'm confused as to how to make the employees show on the projects page.  I've been going in circles for days.  :(

            • 3. Re: Portal Question?
                

              Every table has a PK (= Primary Key) if you want connect a table to another holding specific info you enter a FK (=Foreign Key) which in the graph can be connected to the PK of the table holding the info.

               

              So for your solution:

              Connect PK from Employee to FkEmployee from assignment and

              Connect PK from Project to FkProject from assignment.

               

              If you want to see the employees on a project:

              Create a layout from the table occurrence "Project" (which is your "projects page" ) and ad a portal from the table occurrence "Assignment" and in the row of that portal you enter the field name from the table occurrence Employee.

               

              That should do the trick :)

               

              regards, Menno