3 Replies Latest reply on Oct 26, 2014 8:27 PM by philmodjunk

    table relationship help.

    ericjlindholm

      Title

      table relationship help.

      Post

      I have chosen to restructure my BD to make it more flexible.  It is an event productions management system.  Schedules used to be related to one equipment list on one date but that is not representative of how we work.  

      Now schedules are related to an "event date" record (dates that needs asset)

      I need a way to assign many equipment lists to a schedule.  I have a checkbox set with a list of EQ list names that I can check but it lists the EQ list ID numbers and I cannot seem to create a solid table relationship that will relate to all line items in the checked EQ lists. 

      Is there a way to make this work or a better way all together?

       

      New_ems_structure.jpg

        • 1. Re: table relationship help.
          philmodjunk

          Each record in the schedule links to how many equipment lists? Sounds like you need a table with one record for each equipment list linked to another table with one item of equipment for each piece of equipment on a given list that in turn links to your inventory table (master equipment list if you will).

          • 2. Re: table relationship help.
            ericjlindholm

            It needs to link to one or all equipment lists that are related to the "event" record.  for the example the main room schedule might need to reference a Audio, video and lighting equipment list. 

            my "line items" table has an equipment list ID number.  I wanted to try to link the relate the schedule::equipment lists field to the Line items::  equipment list ID field, but that makes a many to many relationship and it only displays the line items associated with the first list id in the schedule::equipment list field.

            • 3. Re: table relationship help.
              philmodjunk

              Each "scheduled" equipment list needs to be a distinctly different set of records each time you schedule it.

              Schedule-----<EquipmentList------<lineItems>------Inventory

              Schedule::__pkScheduleID = EquipmentLIst::_fkScheduleID
              EquipmentList::__pkEquipmentListID = LineItems::_fkEquipmentLsitID
              Inventory::__pkEquipmentID = LineItems::_fkEquipmentID

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained