How to design a circular relationship with records from a table

[alexsgv]

Hello to all,


I need to create a database to collect data on the associates of a group of people that I manage, and in addition to the classic master data I would also need to be able to enter a relationship between people (people in the same table).
I give a practical example:
Tpeople: (john, mark, sue, roger...etc etc)

I need to make this kind of relationship:

john "is friend of" mark
mark "is the father of" sue
sue "is the daughter of" mark
...and so on

I've tried something but I haven't succeeded and I think I lack some knowledge to be able to do it myself.
I need help to understood how to do it.

I hope you can help me.

bye
alex

[madpiet]

Say you have a table of People (primary key PersonID) Then a relationship would be (PersonID1, PersonID2, RelationshipType) or similar. But that's basically a graph database. (think Facebook or LinkedIn, where there are Level N relationships). I think you can do that in SQL Server, I've just never tried it.

Here's the "general overview" page about Graph Databases in SQL Server:
https://learn.microsoft.com/en-us/sq...l-server-ver16

Sorry for the not so helpful answer, but Access really isn't designed for this kind of thing, unless you want something really simple.

[ArviLaanemets]

Let's assume you have a table where you store the permanent info about persons:
tblPersons: PersonID, ForName, LastName, DOB, DOD, ...,
(You can of-course use a name as ID, i.e. a PrimaryKey for this table, but this is really bad idea, as you easily may get a bunch of different people all with names like John Smith! So having an unique ID for every person is a way to go!)

Now to store any relationships between those persons, you need another table, like:
tblRelationships: RelationshipID, PersonID, RelatedPersonID, RelationshipTyp
(PersonID and RelatedPersonID in this table are a foreign keys which links those fields with table tblPersons. Any person can have any number of relationships with any number of other persons. You have to define an unique index for this table, which doesn't allow to enter the same type of relationship to same other person.)

And probably it will be wise to have a table, where you define all those relationship types too - like:
tblRelationshipTypes: RelationshipTyp, RelationshipText
(where RelationshipTyp is primary key)

This is the simplest design, which assumes, all those relationships are permanent. When this is not the case (e.g. people marry, and then get divorce), the design must have also the start [and End] dates of given relationship (when the end date is empty, then the relationship is currently valid). And the unique ID in tblRelationships must allow to enter the same relationship several times, but only for non-overlapping periods.
So when a person has married, you enter for both of them a record into relationships table, with marriage date as start date, and relationship type as marriage;
When those spouses have later divorce, you update their relation records with divorce date as end date.

[Micron]

maybe
http://allenbrowne.com/AppHuman.html

I was sure that AB also had a page on lineage using horses as an example, but I can't seem to find it.

[alexsgv]

Let's assume you have a table where you store the permanent info about persons:
tblPersons: PersonID, ForName, LastName, DOB, DOD, ...,
(You can of-course use a name as ID, i.e. a PrimaryKey for this table, but this is really bad idea, as you easily may get a bunch of different people all with names like John Smith! So having an unique ID for every person is a way to go!)

Now to store any relationships between those persons, you need another table, like:
tblRelationships: RelationshipID, PersonID, RelatedPersonID, RelationshipTyp
(PersonID and RelatedPersonID in this table are a foreign keys which links those fields with table tblPersons. Any person can have any number of relationships with any number of other persons. You have to define an unique index for this table, which doesn't allow to enter the same type of relationship to same other person.)

And probably it will be wise to have a table, where you define all those relationship types too - like:
tblRelationshipTypes: RelationshipTyp, RelationshipText
(where RelationshipTyp is primary key)

This is the simplest design, which assumes, all those relationships are permanent. When this is not the case (e.g. people marry, and then get divorce), the design must have also the start [and End] dates of given relationship (when the end date is empty, then the relationship is currently valid). And the unique ID in tblRelationships must allow to enter the same relationship several times, but only for non-overlapping periods.
So when a person has married, you enter for both of them a record into relationships table, with marriage date as start date, and relationship type as marriage;
When those spouses have later divorce, you update their relation records with divorce date as end date.

let's assume I understand,
this means I have two links between the person table and the relations table. first one between PersonID(tblPersons) and PersonID(tblRelationships) and second one between PersonID(tblPersons) and RelatedPersonID(tblRelationshps). is that correct?
How do I make this double join?
Alex

[June7]

In query builder, pull tblPersons into design twice. Click and drag between fields to set links.

[alexsgv]

In query builder, pull tblPersons into design twice. Click and drag between fields to set links.

ok, it seems to work



So, next step is to create a form to insert those information in the related tables. What I have to do is to use the query as data origin for the form. is this correct?

[June7]

No. Query is not needed for data entry and would probably just cause confusion. Use it as source for a report to output data.

Bind form to tblRelationships and have 2 comboboxes bound to IDPerson1 and IDPerson2 with tblPersons as RowSource.

If you want to add persons to tblPersons "on-the-fly" during data entry, that is another aspect of this process. And if you want to prevent same person being selected in both, that is another.

I would probably use "partner" instead of "lover".

[ArviLaanemets]

The query in OP's post #7 may be useful too - for reporting. It will display all registered relationships with real names instead of ID's.
June7's post is about form where relationships are registered.

For registering relationships I myself would go with single form based on persons table, which contains a continuous form based on relationships table. User selects person in main form, and in subform all relationships for this person are displayed. And in subform user can edit/add relationships for person selected in main form. You set Link Child/Link Master Fields properties for subform to IDPerson1 and IDPerson, and hide the control linked to IDPerson1 (a textbox) in subform. Whenever the new relationship for person in main form is registered, the hidden control linked to IDPerson1 gets the value of IDPerson from main form automatically.

[madpiet]

Do you mean to imply that there can be more than one relationship between two people? Just wondering, because that's what your index says. If there can be only one relationship between two people, then the unique index would be on (Person1, Person2)

[alexsgv]

Do you mean to imply that there can be more than one relationship between two people? Just wondering, because that's what your index says. If there can be only one relationship between two people, then the unique index would be on (Person1, Person2)

what i mean is that there may be/there is a two-way relationship between two people.
for example:
person1 -- father -- person2
person2 -- daughter -- person1.
same people, different relationship attribute
i don't know if i answered your question.

Alex

[alexsgv]

The query in OP's post #7 may be useful too - for reporting. It will display all registered relationships with real names instead of ID's.
June7's post is about form where relationships are registered.

For registering relationships I myself would go with single form based on persons table, which contains a continuous form based on relationships table. User selects person in main form, and in subform all relationships for this person are displayed. And in subform user can edit/add relationships for person selected in main form. You set Link Child/Link Master Fields properties for subform to IDPerson1 and IDPerson, and hide the control linked to IDPerson1 (a textbox) in subform. Whenever the new relationship for person in main form is registered, the hidden control linked to IDPerson1 gets the value of IDPerson from main form automatically.

I tried your suggestion but I think sub form are to be linked with a query... see example below. I can't see name of person2 but instead are displayed FK.

[alexsgv]

I tried your suggestion but I think sub form are to be linked with a query... see example below. I can't see name of person2 but instead are displayed FK.

here form with its subform linked to query


do you see any problems if I use this way?
Alex

[ArviLaanemets]

here form with its subform linked to query
...
do you see any problems if I use this way?
Alex

In subform, the control linked to IDPerson_2 must be a combo box, with properties like:
ControlSource = IDPerson_2;
RowSourceType = "Table/Query";
RowSource = "SELECT IDPerson, Name & " " & Lastname AS FullName FROM tblPerson ORDER BY 2 ASC";
ColumnCount =2;
BoundColumn = 1;
ColumnWidths = "0;2.5" --probably you have different list separator as ";", so correct this--

Having in subform controls linked to tblPerson directly is probably a reciepe for disaster. Haven't tried this ever, but when it works, then user can change person names in tblPerson at will, or simply delete them!

[June7]

As I said in post 8, including tblPersons in form RecordSource will likely cause confusion (or even allow data corruption). Need a combobox in subform for selecting person.

Not sure what Avril's ORDER BY 2 ASC will do. Maybe should be ORDER BY Lastname, Name

Combobox will allow typing and displaying person name but will save ID. With Firstname first, means typing first name then last name into combobox to select user. If you want to type last name first, consider:

SELECT IDPerson, Lastname & ", " & Name AS FullName FROM tblPerson ORDER BY Lastname, Name

Name is a reserved word and really should not use reserved words as names for fields.

Do you also have a lookup table for relationship types? Again, use a combobox for selection/input of relationship.