7 Replies Latest reply on Apr 24, 2016 9:40 AM by user26705

    Enforce One-to-One Relationship

    user26705

      I have a parent table with several child tables all related by DeviceID.  I would like to enforce that for each parent there is only one child record in each table.  For example if I have Device001 in the parent, then the child will only have Device001. 

       

      Before you refer me to create a unique index on the child...which I can do...my issue is that it is not very user friendly.  I don't want the user to ever get an error stating that they tried to enter a record that already exists.  I would prefer to only allow the Layout to perform the following logic at the layout level:

       

      When the layout opens...

       

      IF a record exists for the DeviceID show that record and allow the user to modify it but not delete it.

       

      IF a record does not exist for the Device ID - then allow the user to enter a new record.

       

      The user should never browse the records or even know that there are other records for other Device ID.

       

      I have been playing with the "Layout_OnEnter" trigger - but can't seem to figure out how to get the DeviceID from the parent table and then enforce the above logic.

       

      Hopefully this made sense and I appreciate any guidance on how-to make this happen or pointers to samples of this working.

        • 1. Re: Enforce One-to-One Relationship
          erolst

          You can check for the existence of a child record simply with

           

          not IsEmpty ( Child::DeviceID ) // there exists (at least) one child record

           

          and use that to display controls, allow/exit script launch etc. programmatically/conditionally.

          1 of 1 people found this helpful
          • 2. Re: Enforce One-to-One Relationship
            brsamuel

            Base your layout on the Parent table, showing related child fields.  Limit creation of parent records to only scripts through Custom Menus.

            1 of 1 people found this helpful
            • 3. Re: Enforce One-to-One Relationship
              bigtom

              user26705 wrote:

               

              I have a parent table with several child tables all related by DeviceID.  I would like to enforce that for each parent there is only one child record in each table.  For example if I have Device001 in the parent, then the child will only have Device001.

               

              I am not sure why you need a child table. If this is 1 parent:1 child, why not just keep all the data in the parent record?

               

              I can only see one small advantage to what you are proposing and it comes at the cost of duplicated data.

              • 4. Re: Enforce One-to-One Relationship
                beverly

                bigtom, there are various reasons to have one-one relationship (too many columns in the table, so split into two tables). When some of the data is more important and there are many records, for example, report finds & sorts would be speedier when performed on the main table only. Then the second table is for data that is less important, but useful at times.

                 

                beverly

                • 5. Re: Enforce One-to-One Relationship
                  bigtom

                  beverly wrote:

                   

                  bigtom, there are various reasons to have one-one relationship (too many columns in the table, so split into two tables). When some of the data is more important and there are many records, for example, report finds & sorts would be speedier when performed on the main table only. Then the second table is for data that is less important, but useful at times.

                   

                  beverly

                  This is pretty much the advantage I was referring to. If the duplicate data and the time to create additional records is worth it compared to the time it saves in finds, sorts, or sending only data you need over slower WAN then it is certainly a thing to consider.

                   

                  Maybe I should not have called it "small".

                  • 6. Re: Enforce One-to-One Relationship
                    erolst

                    bigtom wrote:

                    I am not sure why you need a child table. If this is 1 parent:1 child, why not just keep all the data in the parent record?

                     

                    I can only see one small advantage to what you are proposing and it comes at the cost of duplicated data.


                    It doesn't have to be about duplicating data.


                    There may be singular events in the lifetime of an entity that generate loads of data, and you don't want to keep those attributes in the main entity table.

                     

                    It doesn't necessarily follow from there that the OP is doing this for the right reasons, just that is is a valid setup.

                    • 7. Re: Enforce One-to-One Relationship
                      user26705

                      Regarding why not put it all in in single table?

                       

                      The reason is simply management...and to some extent future proofing.

                       

                      The combination of columns for the child tables is currently 64 - so if I were to add all the columns to the parent - the parent would have a total of 86 columns.  Technically, the computer might not have a problem with that, but humans often do and it is really frustrating to deal with 86 columns when you are doing report and interface design.

                       

                      There is also no way to know what columns will be needed in the future...which could make the parent get really nutty.  Although normalization of data would suggest you have a single table when you have a one-to-one restricted relationship, you would violate the axiom by Date that states "normalize until you die, optimize until you get it right."  Lastly, each child table is focused on a different contextual type of data - so group the columns by context allows for betting management and you can add columns in the future without taxing the parent, by allowing the children to grow - kind of like life.

                       

                      Hope that helps.