1 Reply Latest reply on Mar 1, 2012 3:20 PM by philmodjunk

    Too Many Relationships?



      Too Many Relationships?


      I am building a datebase solution to track keys and I think I am doing somethis wrong. My current structure is:


      As in each key belongs to one of a few groups (1st floor, 2nd floor etc.) Then each key has a name for the room it unlocks and then there are instances of each key.

      To prevent duplicate data each database points to the database above it so for every record in KeyIndex it points to a record in KeyName and then KeyName points to record in KeyGroup.

      The problem comes when I try to point to data for a key index but I am pointing to data several relationships away. Is there a way to store all of the data within KeyIndex but since data would exist multiple times have it change if I modified its parent? So if I have a KeyGroup field in KeyIndex, a lot of keys would have the same KeyGroup but if that changed in one location have it update accross all of the instances?

        • 1. Re: Too Many Relationships?

          I'm guessing that you have three tables linked like this:

          KeyGroup----<KeyName-----<KeyIndex   (---< means one to many)

          Keygroup::GroupID = Keyname::GroupID

          KeyName::RoomID = KeyIndex::RoomID

          With one record in KeyGroup for each group of keys, one record in keyName for each lock (or group of locks) that is keyed and one record in KeyIndex for each key that exists.

          Whether you can access data in another table will depend on the table context determined by the layout that is current at that moment.

          From a layout based on KeyIndex, accessing the data related to that key in KeyName and KeyGroup should be simple and straight forward.

          On the otherhand, accessing data in KeyName from KeyGroup will be a problem because a single record in KeyGroup links to many records in keyname and each might store different values in their fields. In FileMaker a reference to a field from KeyName from KeyGroup will access the "first" related record. The first related record will be the oldest related record unless a sort order has been specified in the relatinship, in which case the related records that sorts first in the specified sort order will be the first record.

          As you can see, a lot depends on the details of your relationships and the layout from which you are attempting to access data. You'll need to spell out more of those details in order for someone to be able to tell you why the data is not accessible in the manner you expected.