4 Replies Latest reply on Nov 21, 2013 11:50 AM by ChrisJohnston

    Moving core, and all helpful reading

    ChrisJohnston

      Title

      Moving core, and all helpful reading

      Post

                          I broke this in to its own topic because the way FileMake displays long threads displays makes it difficult to for me to follow and load it because it always jumps back to first post. The original was Encapsulation and Breadcrumbs

                          Another FileMaker user told me this

                          "A join table is required only if the relationship is a many-to-many.- meaning that a topic can have many children AND many parents. If that's not true in your situation (and I think that is what you are saying), then the join table is doing nothing except complicate things. You should link a child topic directly to its parent (or root) in a self-join:

                          Topics::TopicID = ChildTopics::ParentID"

                          Is this the same thing you were saying? This

                          

      Information-----<Information_Topic>-----Topics------<Topic_SubTopic>--------SubTopics

                          

                               TO's in green all have the same data source table, Topics. TO's in Blue all have the same join table as a data source table.

                     
                          

                               Now brace yourself. I've been rethinking this a bit and there's a design option that just can't be ignored here. If you want to lock this basic structure down into only a tree structure, no "webs" permitted, you could simplify the relationships down to three TO's and only one date source table:

                          

      Information-----<Topics------<SubTopics

                          

                               Information::__pkTopicID = Topics::_fkParentTopicID

                          

                               Topics::__pkTopicID = SubTopics::_fkParentTopicID

                          

                               The key difference, besides being MUCH simpler, is that with this structure, a given record can only link to a single Parent Topic. The original design allows for the possibility that a given topic could be linked to two records at the "information" level.

                          

                                

                     
                     This will mean revamping the relationship structure lot but if better I will change to it immediately. I will not hardly any work except, maintanance because every we our happy with our UI and features. Will I be able to have unlimited Subtopics with this setup?                

                           

                          Also I don't know if this needs its own topic but If you say yes I can start one, but do have a listing of the links to all your reading like your Tutorial on Table Occurrences?

                          this one

                           

                           

            

        • 1. Re: Moving core, and all helpful reading
          philmodjunk

               I can't tell if it is "better" or not. It IS simpler and Okham's Razor is not to be dismissed lightly when designing a database, but the second design also has limitations not found in the original set of relationships. You'll have to decide for yourself whether the limitations of the simpler relationship set will be a problem for how you need to work with this data.

          • 2. Re: Moving core, and all helpful reading
            ChrisJohnston

                 No! I don' t expect you to tell if better, that is specific to what we have to do and have going on. Our use and result will determine if it is better, I am just asking if the

                 Topics::TopicID = ChildTopics::ParentID

                 is the same thing you were suggesting. I have given it some serious thought and we don't need "webs" like I had told you would be an option to keep open. So I think I am going to try this setup and I think like you are suggesting I can benefit from it's more simpler structure.

                 So to be clear our "root" Information can have no parent. Our Topics of that Information can have unlimited sub topics. Correct?

                 I get it now the coloring of green, means all Same Data Source! Got It. So it is kinda similar to the one that supports webs, but minus the many to many.

                  

            • 3. Re: Moving core, and all helpful reading
              philmodjunk

                   It's the same basic relationship. What I'd shown are three table occurrences with the same data source table. That was so we could show data from child records from SubTopics while also showing parent data from Information on a report that lists data from Topics.

                   Thus you can "recurse" through the relationships as many times as necessary to work your way down the "tree" from parent to child.

              • 4. Re: Moving core, and all helpful reading
                ChrisJohnston

                     I got it! Excellent work as usual! Thanks