3 Replies Latest reply on Mar 31, 2015 4:17 PM by philmodjunk

    Relational modeling 2 or 3 tables



      Relational modeling 2 or 3 tables



      developing a data base to hold access codes. , not concerned about security issues and primarily used to make sure I do not issue the same code to more then one user and not to reuse the same code over once removed from a system

      one user can have access to many systems.

      A example of a system is Alarm, cameras, gates, key keepers, wifi access points, routers, keys ect. 

      A system can have many locations, gate 1, gate 2, gate 3, alarm 1, alarm 2, wifi 1, wifi 2 , door 1 door 2 ect.

      The ? is should I have 2 tables or 3

      One user to many locations/passcodes and in just have a lookup in locations as to what system it is

      Or 3 tables user to system and systems to locations/passcodes





        • 1. Re: Relational modeling 2 or 3 tables

          Isn't this correct? One user be granted access to many systems. A given system can, of course, have many users.

          That's a many to many relationship.

          And am I correct that a given location can only be part of one system?

          I'm coming up with 4 tables here, not 3 and certainly not 2.


          The Join table is used to implement a many to many relationship between users and systems.

          • 2. Re: Relational modeling 2 or 3 tables

            Little fuzzy coming from Lotus if your that old and dusty access.

            Do I need to auto populate the field of the fk in join from pkuser  and if so do i do that with the check boxes within relationships dialog box and which ones?

             join of __pkUserID to _fkJoinUserID

            • 3. Re: Relational modeling 2 or 3 tables

              Start with these relationships:


              Users::__pkUserID = User_System::_fkUserID
              Systems::__pkSystemID = User_System::_fkSystemID

              You can place a portal to User_System on the Users layout to list and select  Systems records for each given Users record. Fields from Systems can be included in the Portal to show additional info about each selected Systems record and the _fkSystemID field can be set up with a value list for selecting Systems records by their ID field.

              Enabling "allow creation..." for User_System in the Users to User_System relationship will enable you to create a record in the User_System table by entering data into any row of the "add row" found in the portal to User_System. Thus, you can create the record and link it to both Users and Systems by formatting the _fkSystemID field with a value list for selecting a system.

              You can also turn this around and work the same time of set up from a layout based on Systems where you select a user for the system from the _fkUserID field.