0

Relationship between a table and itself

Hi,

 

Is there a way to have a relationship between a table and itself without having to duplicate the table. An example of this would be a "PERSON" table and a table "RELATIONSHIP", where the latter can store the relationship(s) between "person A" and any other person in the "PERSON" table.

I would prefer this to be handled without having to make a copy of "PERSON".

Thanks for your feedback.

Cheers,

 

Jacques Kinnaer

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • There is no barrier in Ninox preventing the creation of multiple relations between the same set of tables, or even between a table and itself. While it is always a good idea to rename the reference fields to give them names that indicates their purpose, it is especially important when several relations exist between the same set of tables.
    Suppose, for example, that the "RELATIONSHIP" table is used to record parents-children relations. When creating the first N:1 reference in "RELATIONSHIP" to "PERSON", the reference field will be named, by default "PERSON". If you create a second one, it will be named, by default, "PERSON2". So, which is which?
    I would thus suggest, after creating the first reference in "RELATIONSHIP", to immediatly rename the reference field "Parent". Then open the "PERSON" table and rename the newly created 1:N reference field, named by default "RELATIONSHIP", "Children". Open the "RELATIONSHIP" table again, create the second N:1 reference to "PERSON", and immediatly the name of he reference field from "PERSON" to "Child". And finally open the "PERSON" table and rename the newly created 1:N reference field, named by default "RELATIONSHIP" again, "Parents". Of course, other names should be chosen if another kind of relation is being represented.

    Like
  • Thanks! It's that simple. Great. That helps me very much.

    Like
    • svs
    • svs
    • 1 yr ago
    • Reported - view

    I'm struggling at this same point. I can create the simple relationship above. How do I expand it? I need to know parent-child, husband-wife, uncle-nephew, etc. By trying to duplicae the above with the PERSON and RELATIONSHIP tables I'm getting loops and linking is going haywire.

    Like
  • Hi all. First of all it's not quite as simple as it may seem. It is a many-to many join with Person - Relationship - Person so you may have some data that looks like this

     

    Peter - Father/Son - David

    David - Brother/Sister - Jane

     

    The trouble is, when you want to see all the relatives of David, his father is on the left but his sister is on the right and so you can't have a single view showing both. It might be better to have two joins for each

     

    Peter - Son - David

    David - Father - Peter

    David - Sister - Jane

    Jane - Brother - David

     

    Now we can have a single view showing both relatives of David. So now the question is, how do we create and maintain this and I would suggest that maybe a version of the first model should generate the second.

     

    Now to model it!

     

    Regards John

    Like
  • So this works. The fields were hard to name, so sorry if they don't make immediate sense.

     

    Screenshot 2021-06-16 at 19.37.53

     

    Screenshot 2021-06-16 at 19.38.17

     

    Screenshot 2021-06-16 at 19.38.54

     

    I have added a reference to link the pairs in the Relationship table. That way when one is updated the button will create or update the other. Set Composition to Yes and deleting one will delete the other as well.

    Regards John

    Like
    • svs
    • svs
    • 1 yr ago
    • Reported - view

    Many thanks for your response. I don't really understand what the code is doing. Nor which relationship is composite, but thank you. I hadn't realised that this would be so problematic. I am not a coder so this is beyond my skillset.

    Like
  • Having made one relationship

    Peter - Son - David

    and noting the that reverse relationship is Father (the Reverse field) the button is creating or updating the second

    David - Father - Peter

     

    Lines 1 - 6 copies the data to variables

    Line 6 checks to see if the other half of the pair has been created

    Line 7 creates the other half

    Lines 9 - 12 copies the data from the variables

    Lines 13 and 15 pair up the two records so one can always be found from the other

    Line 16 from here on, a pair already esists

    Line 17 finds the other record of the pair

    Lines 19 - 22 updates the data from the variables

     

    Regards John

    Like
    • svs
    • svs
    • 1 yr ago
    • Reported - view

    Thank you

    Like
Like Follow
  • 1 yr agoLast active
  • 8Replies
  • 539Views