5 Replies Latest reply on Apr 14, 2014 12:12 PM by philmodjunk

    One to one relationship - is it a problem?

    appt

      Title

      One to one relationship - is it a problem?

      Post

           So we have developed and deployed a solution to a local primary school. They have been using it for six months and they love it. 

           It records lots of data, test results, anecdotal notes etc about each child. Because there literally thousands of fields we split them into tables (reading, writing etc).

           Of course from a design point of view this is wrong as each child only has one set of data so it should all go in one table but that would have been impossible to work with (over. 1000 fields).

           it has not been a problem so far but I know potential is there to create a second record for a child. It is deployed on iPads so menus that allow add new record are locked off and as I say it has not been a problem in 6 months with 20 teachers.

           any thoughts? And is there a way to force a one to one relationship in the graph?

        • 1. Re: One to one relationship - is it a problem?
          philmodjunk

               Why would it be "wrong"? It works doesn't it?

               Functionally, having two tables linked in a one to one relationship is nearly (not quite) identical to putting all the fields from those two tables all in a single table. It's a trade off of having shorter field lists in your table schema vs. a more complex relationship map.

          • 2. Re: One to one relationship - is it a problem?
            appt

                 Thanks Phil,

                 all the books and forums say it is a no-no, so I was just looking for anything I may have missed since it is working great. 

            • 3. Re: One to one relationship - is it a problem?
              philmodjunk

                   Sorry, but in many years of database work, never saw any such warning stating that it was a bad idea and thus can't see any reason why it's "wrong" to have two tables in a one to one relationship.

              • 4. Re: One to one relationship - is it a problem?
                appt

                     From the Filemaker Missing Manual

                     "As a general rule, unless you can articulate a good reason for its existence, a one- to-one relationship is a mistake: It’s just two tables where one would suffice. (For some clarification, see the box below.) You’ll almost always want to combine entities like people and their addresses into one table."

                • 5. Re: One to one relationship - is it a problem?
                  philmodjunk

                       "unless you can articulate a good reason"  would seem the operative phrase there. In my opinion, that sentence overemphasizes the negative by calling it a "mistake".

                       Adding a second table linked in a one to one relationship  complicates your relationship design so you don't want to do this unless you have a good reason. But you already indicated that this was working for you and your reason for making this design decision.