5 Replies Latest reply on Aug 29, 2011 3:16 PM by philmodjunk

    Newbie design problem



      Newbie design problem


      I manage an office building full of suites and each suite has multiple rooms.  I have constructed a table with the fields "suite number," ‘room number," and a unique “ID number” for each room to use as a primary key, as room numbers repeat in multiple offices. 

      The rental staff provides me with a constantly changing list of who the tenants are.  I can turn that in to a table with the fields “Tenant name, “Suite number”, and”Room Number”.  I would like to add the “ID number” field to this table as well.   

      How do I set it up so that this second table takes the suite and room numbers, looks them up in the first table, and then and inserts the corresponding room ID numbers in this table?


        • 1. Re: Newbie design problem

          It's better to turn this around. Remove the Suite and Room number fields from the tenant table (which also needs an TenantID field), and just link your tables by RoomID number. When you need the suite and room number, refer to those fields in the related table.

          Note: you may need to rethink that basic relationship here. Is it possible that you will need to record more than one Tenant for a given room? Is it possible that a Tenant can rent more than one room? Your answers to those two questions will dictate how to link your two tables.

          If the first answer is yes and the second no, you have a one to many relationship of Rooms to Tenants and both tables should be linked by a TenantID. If the first answer is no and the second yes, you have a one to many relationship of tenants to rooms and your tables should be linked by a RoomID. Most likely the answer is yes to both questions. In such cases, you have a many to many relationship and you should link your tables by linking both to a Join table:

          Rooms::RoomID = Tenant_Room::RoomID

          Tenants::TenantID = Tenant_Room::TenantID

          With this set up, a Rooms layout can list all tenants for that room in a portal to Tenant_Room and a Tenants layout can use a portal to Tenant_Room to list all rooms leased to that tenant.

          • 2. Re: Newbie design problem

            Thanks for the help PhilModJunk.  The answer to both questions is yes, I'm glad you pointed that out.  Getting back to the original problem:

            The rental staff refuses to have a Room ID field, and will only provide me with a list of tenant name, suite number and room number, so I have to add the corresponding Room ID based on their fields before I can link them.  That is the big problem. 

            • 3. Re: Newbie design problem

              The rental staff should not be required to have anything to do with a room ID field. This should be an internal field used by your database to link the records. (This is a Primary key field and true primary keys should never come from outside your database, but always be automatically generated by your database.)

              This now sounds like more of a layout design issue than a relationship.

              Can you spell out the exact process of  what you need to do when the rental staff gives you that list?

              Is this a list in electronic form that you can import?

              Is this something you can set up so the staff enters this data directly into the database for you?

              There are a number of methods you can use where you enter/select text such as room number and the system finds the corresponding primary key and enters it where needed in the foreign key fields of related tables in your database. The best method will depend on what exactly you need to do here.

              • 4. Re: Newbie design problem

                Can you spell out the exact process of  what you need to do when the rental staff gives you that list?

                1.  The Rental staff gives me an Excel spreadsheet with the three following columns: Name, Suite #, Room #.  Names can rent mulitple rooms, rooms can have multiple Names.  Entries are repeated on the list as required

                2.  I can import this spreadsheet and turn ito a table "Tenant" with the corresponding three fields.  (They will not enter it directly in to the database)

                2.  I need to to take the Suite # and Room # and lookup the corresponding Room ID#(primary key) from another table that is essentially fixed and unchanging.

                4.  I then need to stick this looked up Room ID into the fouth field of "Tenant"


                Thank you so much for this help.



                • 5. Re: Newbie design problem

                  We'll call that other table "Room Reference".

                  Set up this relationship to tenant:

                  Tenant::Suite # = RoomRefBySuiteRoom::Suite # AND
                  Tenant::Room # = RoomRefBySuiteRoom::Room #

                  If you enable auto-entry options during import, you can set the RoomID field up with a Looked up value field option to copy the matching record's RoomID from RoomRefBySuiteRooom. (RoomRefBySuiteRooom is an additional occurrence of Room Reference so that existing relationships between Room Reference and Tenant are not affected by this relationship.)

                  And if you find that you need your join table, you'll need to start up a script after import that loops through the newly imported tenant records and created the needed entries in the join table.