Two Links Between Same Two Tables

[SigridM]

New to Base, but not to database design…

I am creating a mailing list database where individuals are stored (Person table). I also have a CoupleRelationship table to store an information relevant to any two individuals in a committed relationship. The CoupleRelationship table has its own ID, plus Person1ID, Person2ID and their anniversary date.

In Relationships, when I try to link the ID of the Person table to Person1ID, no problem. When I try to then link the ID of the Person table to Person2ID in the CoupleRelationship table, I get a dialog box saying, “this relationship already exists” so I click on “Create” to create a new relationship. Then I see two links between the CoupleRelationship table and the Person table. Good. This is what I want. I save the relationships, then save the database.

When I go back into Relationships, though, only the first link is there. Is this a bug in Base, or am I doing something wrong? Is there a better way to establish this relationship?

Thanks!

[RobertG]

Two links from the same primary key of a table to another table won’t work. The relation is defined by the first connection.

Have constructed this for a database to get groups of persons like families:
Set a field “GroupID” in the table “Person”. Link from “PersonID” to “GroupID”. First person will set this “GroupID”. Second person will get the “GroupID” from the first person. and all other members of the group also.

Don’t understand why you need such a construction in a mailing list database and why you only need a connection between 2 persons.

[Villeroy]

This type of relation works well with HSQL but can not established in the Base GUI.
See attachment where I added a table “Partners” in SQL:

create table "Partners"(P1 INT NOT NULL, P2 INT NOT NULL, DT DATE, NOTE VARCHAR(100), ID INT IDENTITY, FOREIGN KEY (P1) REFERENCES "Persons"("ID"), FOREIGN KEY (P2) REFERENCES "Persons"("ID"))
alter table "Partners" add constraint "Selfish_Love" check (P1<>P2)

http://www.hsqldb.org/doc/1.8/guide/ch09.html
Married_Couples.odb (18.8 KB)

[SigridM]

@RobertG

Set a field “GroupID” in the table “Person”. Link from “PersonID” to “GroupID”. First person will set this “GroupID”. Second person will get the “GroupID” from the first person. and all other members of the group also.

Thanks for your reply, Robert.

I think I only partly understand what you’re saying. Yes, I could put, say, the “RelationshipID” into both Person records for this particular use case. But I’m not sure what you mean by “First person will set this…[and] Second person will get [it] from the first person.” Do you mean when I set up the data entry form?

Don’t understand why you need such a construction in a mailing list database and why you only need a connection between 2 persons.

For this particular use case, it’s because we print a calendar that includes birth dates and anniversary dates. The anniversary applies to a couple, so it would be error-prone to have to put it in twice, once for each member of the couple (or delete it from both should the relationship end).

I have another, similar, use case for a parent-child relationship. In that case, it doesn’t work to put all of the members in the same “group” (aka, family or household, although I am also doing that for other reasons) because we want to be able to distinguish who are the parents and who are the children in the household (until those children grow up and establish households of their own). In that case, I was planning to have a Parent-Child-Relationship table, but again, both would have to link back into the Person table with a ParentID and ChildID from the Parent-Child-Relationship table linking to different PersonIDs in the Person table. And so far, that doesn’t work, either.

[SigridM]

Thank you, Villeroy. This looks like exactly what I was looking for. (And I love the name of your constraint! Lol!)

[RobertG]

@Villeroy : Have copied the code. Will be published with comments in next German Base Handbuch.

[Villeroy]

It can be done in one statement: CREATE TABLE(<column declarations>, <foreign keys>, CONSTRAINT "Selfish_Love" CHECK (P1<>P2)).