2 Replies Latest reply on May 11, 2015 7:27 AM by nicolai

    Help setting up new database



      Help setting up new database


      I am setting up a new database which records workshops and contacts. Each workshop needs to have a list of attendees made up of contacts and each contact needs to have a list of workshops attended. 

      I have mapped it out and need a many to many relationship between the two tables which I do not want but I can not work out the right tables to create. 

      Each contact can attend many workshops and each workshop will have many contacts attending. 

      Can anyone make any suggestions? 

        • 1. Re: Help setting up new database

          The minimum you need:

          1. One table with "Contacts" (Fields: a) ID b) Name - plus all the other fields you need

          2. One table with "Workshops attended" (Fields: a) ID field for that table b) IDContact c) other fields you want to see in that table

          3. Now you will link the ID from your "Contacts"-Table with the IDContact from your "Workshops attended"-Table

          This is a so called One-to-Many relationship

          The ID in your contacts-Table must be unique and don't forget to allow Record generation thru the Relationship ("Workshops attended")

          This  set up will allow you to see all the workshops one has attended at a glance (in one form) and you can add attended Workshops thru this form as well.

          Next steps: One table with all the different workshops there might be




          • 2. Re: Help setting up new database

            I can suggest a very standard solution. This is called resolving many-to-many relationships with "join table" in case you would like to read further.

            Provided your Contacts and Workshop tables have primary keys, lets say ContactID and WorkshopID. Create another table, let's say "Attendance" with the fields AttendanceID (primary key),  ContactID and WorkshopID (both will be foreign keys).

            To create an attendance record you will need to populate a record in this table with ContactID and WorkshopID.

            Create relationships:

            Contacts  => Attendance (Contacts::ContactID => Attendance::ContactID )

            Attendance => Workshop (Attendance::WorkshopID => Workshops::WorkshopID )

            This way if you place Workshops portal into Contacts layout you will only see the Workshops with current Contact attends and if you place Contacts portal into Workshop layout you will only see Contacts which will attend the current workshop.