5 Replies Latest reply on Jul 31, 2010 2:20 AM by DanDan

    Simple relationship queries!



      Simple relationship queries!



      Although filemaker 11 allows it, is it okay to have two relationships from a key field to two other occurances?

      For example, I want to add a table to my database that keeps track off customer subscriptions.

      Using the starter template 'invoices' I added a subscriptions table with subscription id, customer id, product id and product name fields.  The idea being I could attach multiple subscriptions to each customer and look the products up which they subscribe to via the products field occurance.

      Initially I had the subscriptions table linked to a customers and products table occurance, but realized by doing this I could not add the subscriptions record entry fields on a tab in the customers layout which is what I wanted.  So I had to create a relationship between the base customer table and the subscriptions table via the customer id field.  The problem, or maybe not a problem, was that the customer id field in the customer base table is already linked to the similars table occurance.

      In other words, I would have two relationships from the same customer id key field to two different tables.  Whether it works or not I don't know but it doesn't sound very good practice.

      Hope all that makes sense.  My wanted solution is to have a seperate table for subscriptions, but have the record entry layout on the same tab control as the customer page, so at a glance I can see all subscriptions for that customer.

      Any guidance welcomed.




        • 1. Re: Simple relationship queries!

          There should be no problem with that and it's very common practice in database design.

          • 2. Re: Simple relationship queries!

            Your graph looks fine. I'm assuming that the Similars "does not equal" is between CustomerIDs (eliminating current customer from the match). 

            What you're using is the "anchor buoy" method of graph organization, with table occurrences (TOs) organized in non-connected table occurrence groups (TOGs). 

            A further enhancement of this organization is to use a consistent naming convention, which identifies the location of a TO, both within the overall graph, and within its own TOG. Some people do not do this, prefering more natural "functional" names. But one can compromise. Here's how I'd name them:


            The "INV__" identifies the TOG and makes the main TO of a TOG sort to the top of its TOs. Including all the TOs in the path lets you see that the Products TO above is only accessible THRU LineItems. Only the target TO of a line is capitalized, and is at the end. 

            One other convention I use is singular or plural to denote a child relationship to a primary key, ie., targeting only one record. "inv_items_Product" is targeting only one record in product from a particular record in inv_LineItems. Yes, many records could be targeted "thru" LineItems (from INV__Invoices); but not directly from one LineItem record. My thinking is that we use singular and plural all the time in natural speaking, why not use it here also.

            While it is true that FileMaker usually separates the TO lists into "related" and "unrelated", it does not always do so. If they all sort alphabetically it is much easier to find one, and know whether it is connected and how.

            Sometimes I name things a little "backwards," but it makes it easier to find things, as similar fields are also grouped. But one needs to be a bit flexible here. I use a tilde "~" to specify fields, at the end of relationships, and only do so when necessary. But that's just me.


            I would also name the fields: CustomerID_billing, CustomerID_shipping.

            • 3. Re: Simple relationship queries!



              Thanks for all comments, especially Fenton Jones for the details on his own preferred method.

              I do prefer the anchor buoy method, I think it allows for a clearer understsanding of the design and tables relationships, which is better when you're a noob like me and very useful in large and complicated database designs. Though I'm sure there are pros and cons.

              To be honest, I don't know what the 'similars' TO is for, it was already in the design I started with. I would have preferred to remove it.

              • 4. Re: Simple relationship queries!

                Similars is included in many of the FileMaker templates. It is an attempt to show possible duplicates. Though, in reality, this is not so easy, as relationships only include exact matches (per line), so using an entire "name" means it would only match if you typed it exactly the same. 

                Since you are only looking for "possible" matches, I've found it useful (when I had to do such a thing) to include other fields also, in the multikey; such as Email(s), Phone(s), 1st part of the address, etc.. Phones should be "cleaned", ie., stripped down to numeric only. I did not use a pre-filter, just showed ANY possible match. Nor would I use such things as "city" on its own.

                The principle here is that with a multi-key, ie., return-separated values, if ANY line matches, the relationship matches. So you can include somewhat dissimilar values, emails, phones, etc..

                There is a big difference between "possible" matches and "identical" matches. Each is useful in its own way.

                The downside to an inclusive "possible matches" is that the multi-key must be a stored calculation field, so it adds to the file size. This can be somewhat ameliorated by using only the 1st part of long text, especially addresses (the last part of addresses is so inconsistent as to be useless for this).

                One way to use this is to have a dedicated "new person" layout, where you first enter the person's data into global fields, which are then matched against existing data for possible matches; shown in a portal (or portals, if separate tables, Phones for example). When a person sees that the person really is new, they hit a button, and whatever they entered into the globals can be set into a new record (or records if multiple tables).

                • 5. Re: Simple relationship queries!

                  Arrr right, that makes sense now, well explained!

                  Thanks for taking time to write that, I couldn't find anything about it anywhere else.