Link Rows in Same Table

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Link Rows in Same Table

Post by TheNoLifer » 2016-04-12 18:18

Hello everyone,

Looking for some thoughts on this issue;

I've got a "Persons" table with one row per person. Row_id, Title, Firstname, Lastname, notes, that sort of thing.

I've now had a requirement to "link" persons together based on their relationship to other people in the table. So for example;

1 | Mrs | Elizabeth | Smith
3 | Mr | Douglas | Smith

They could be husband and wife. How can I show their relationship in the Persons table?

I thought about creating a new table - Person_Relationships - it's a child of Person table. It has an ID, a "relationship type" column and two lookups to Person table. It allows you to do something like this;
relationship.JPG
relationship.JPG (71.75 KiB) Viewed 6917 times
However! When you view one of these people in the parent Persons table it looks like this (due to the two joins for the two lookups I guess);
relationship_2.JPG
relationship_2.JPG (105.05 KiB) Viewed 6917 times
Not a good solution. Any ideas how I could show a relationship between two people in this table?

Thanks!

A.

peteraj
Posts: 27
Joined: 2016-02-05 12:25

Re: Link Rows in Same Table

Post by peteraj » 2016-04-13 07:35

Hi,
Could the problem be that "son of" only works one way? George A is "son of" George I, but George I is "father of" George A, or vice versa..... I looks like it will work well with "brother to", "married to", but i other cases, you need to "reverse" the relationship......
Does that make any sense?

/Peter

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Link Rows in Same Table

Post by TheNoLifer » 2016-04-13 14:51

Hi Peter,

That makes sense! Good point!

However from a technical AppGini standpoint, I don't know how to get around the two lookups to my Person table - you need to be able to pick Person A and Person B to define their relationship (whatever it is), but in doing so you get these two tabs (due to the two joins) which is confusing to the users.

Exploring options for a nice, clean way to link two records from this table - hoping someone has implemented something similar.

Cheers,

A.

peteraj
Posts: 27
Joined: 2016-02-05 12:25

Re: Link Rows in Same Table

Post by peteraj » 2016-04-13 17:48

Hi,

I am not an expert on parent/children databases, so there might be a smarter way to do it! But why not create only one table, and make person 2 look up into person 1? like this;
relations.PNG
relations.PNG (7.01 KiB) Viewed 6888 times
If you wan't to save the users from creating 2 records (eg Father and son) you could add some code to the tablename_before_insert and tablename_before_update hook, so that when a person A was added with person B as father, the database would automatically add a record with person A as son to person B!

I hope I have made myself clear? :-)

Regards
Peter

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Link Rows in Same Table

Post by TheNoLifer » 2016-04-13 18:49

Hi! Thanks again for your thoughts!

If I understand correctly and you do it that way, you can only link 1-1 - e.g. Person 1 linked to Person 2 (husband). What happens when you also want to link Person 1 to Person 3 (son) and Person 1 to Person 245 (Great-Aunt) :)

I'm sure I'm missing something about this design - there must be a way to accomplish this easily!

Thanks,

Alasdair

peteraj
Posts: 27
Joined: 2016-02-05 12:25

Re: Link Rows in Same Table

Post by peteraj » 2016-04-13 19:48

You are right Alasdair, my solution can only link 1-1. I see 2 possible workarounds, 1) Add a number of relationship fields to the table (would have to be many, as some people has a large family!) or 2) Create a record for each relationship. That woud mean that each person would be representet many times in the table, but I think that solution would be more flexible! I have no idea of how to present that in a "smart" way (I am more of a database man! ;-) ) But maybe if you tried to draw a picture of what you want the result to look like, a clever head in here, could point out the solution?

/Peter

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Link Rows in Same Table

Post by TheNoLifer » 2016-04-14 04:37

I have solved it! My solution is a bit... weird, I guess, but it works.

I have my Person table, which is parent.

I created a table called Relationship List and one called Person Relationships

Relationship List is a hidden/admin table which looks like this;
1.JPG
1.JPG (90.42 KiB) Viewed 6873 times
It will have a bunch of rows for every person in the system. Will be a big table! I am thinking about using PHP/SQL to pre-populate this table somehow, like create a row for every Relationship type for every Person - I'm not sure how to do this, but one step at a time!

So then I have the Person Relationships table which looks up the Relationship List for the Relationship Column, and the main Person table for the "Primary" Person.
2.JPG
2.JPG (86.2 KiB) Viewed 6873 times
So when I view the children records from the main Person table, I can make it look like;
3.JPG
3.JPG (109.89 KiB) Viewed 6873 times
It's obviously one way - I'd need to go into the other people in those children records and set up their corresponding relationships back to this person.

The most annoying part is having the Relationship List table and having to populate it manually right now with all the possible combinations of relationship. Need to think about how to create those rows.

Hope this makes sense!

Cheers,

A.

primitive_man
AppGini Super Hero
AppGini Super Hero
Posts: 54
Joined: 2014-03-09 20:20

Re: Link Rows in Same Table

Post by primitive_man » 2016-05-17 09:59

What you're looking at here is the recursive generation of relationships - the type you typically see in 'Family Tree' software.

It's also extremely difficult and tedious to explain via text.

The biggest problem you'll face is that it is exponentially regenerative, for example:

In a family tree of only three generations a new member is added - then every link (family member) above that new member will have to be re-assessed and new relationships created...
For Primary Father: New Addition 'Father of',
For Primary Fathers' Father: New Addition of 'Grand-Father of',
For Primary Fathers' Fathers' Brother: New Addition of: 'Uncle of'....

when you factor in Sisters, Mothers the list goes on and on...

... ad infinitum, at least textually - because the relationship names after around 5 generations would be laughably long.

primitive_man
AppGini Super Hero
AppGini Super Hero
Posts: 54
Joined: 2014-03-09 20:20

Re: Link Rows in Same Table

Post by primitive_man » 2016-05-17 10:00

This is the reason why pretty much all family tree software uses diagrams to define relationships and not text fields.

Post Reply