1 Reply Latest reply on Jul 9, 2015 9:01 AM by philmodjunk

    Advice needed

    SteveKeiser

      Title

      Advice needed

      Post

      A portion of my database keeps track of equipment rentals. There is an Equipment table and a Rental table that relate to each other in a one to many relationship. When a new Rental record is created, _EquipID_fk is selected in the Rental table which relates to _EquipID_pk in the Equipment table. Then a location is selected as _FacilityID_fk in the Rental table. This fires off a script which updates the location in the Equipment table. When various locations want to know where their equipment is located there are sometimes errors because the location specified in the Rental table and the Equipment table sometimes doesn't agree. I am tempted to fix this by changing the location field in the Equipment table to be a calculation of Last ( Rental::_FacilityID_fk ). The side of me that wants to fix things right is telling me that when a new record is created in the Rental table, the location selected should really be selecting the location in the Equipment table and then a lookup sets this location in the Rental table. Since I can't really see that far down the road as those of you who do this thing on a larger scale, I don't know if it is worth all the work to change things to work the way I probably should have set it up in the beginning. Any advice would be really appreciated.

        • 1. Re: Advice needed
          philmodjunk

          Your post definitely illustrates why you don't want to store the same data (location) in two tables. The whole point to a relational database design is to store such data into exactly one table.

          You may not need any field for location in the equipment table at all if you set up either a relationship or portal correctly.

          I'm guessing that you have these relationships:

          Equipment----<Rentals>------Locations

          If you specify a sort order for the relationship between Equipment and Rentals that makes the most recent rental record the first related record, you can simply include any needed fields from Locations on your equipment layout and they'll show location data as specified for the most recent rental.

          Alternatively, a portal to rentals on the Equipment layout can include fields from Locations. You can specify a sort order for the portal that lists the most recent rental first and then you can check the top row of this portal to see a rented item's current location.