Table relationships

Discuss the database features

Table relationships

Postby sjskhalsa » Mon Apr 29, 2019 2:15 am

I'm a newcomer to OO Base, but not to RDBMS. I'm want to be able to relate a concept to either an author or a work. And I want to be able to label that relationship ("agree", "disagree", "neutral"). This is the ERD of what I've done so far.
ERD_test_relats.jpg

My issue is creating the forms to enter data. If I create a form for entering concepts I want to be able to pick from either the author or works list and assign an agreement, and have an entry in the respective linking table automatically populated. I tried to use subforms and listboxes to accomplish this, but I get errors and frankly not sure how to proceed.

Thanks ahead for any help rendered.

Note: A work can have 0:n authors, but I don't know how to change cardinality with the relationships GUI)
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 5
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby Villeroy » Mon Apr 29, 2019 3:15 pm

IMHO, this is another many-to-many relation just like the one between Authors and Concepts.
Delete the relation between Authors and Works and run the following in menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Authors_Works"(
  AID INT,
  WID INT,
  PRIMARY KEY(AID,WID),
  FOREIGN KEY (AID) REFERENCES, "Authors" ("ID"),
  FOREIGN KEY (WID) REFERENCES, "Works" ("ID")
);

Then call menu:View>Refresh Tables

P.S. anticipating your question on forms: viewtopic.php?f=13&t=97331&p=466619
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Table relationships

Postby UnklDonald418 » Mon Apr 29, 2019 7:17 pm

As long as the "IDAuthor" field in "Works" does not have a NOT NULL constraint, then 0:n would be allowed despite what is shown in the GUI found at Tools>Relationships. The GUI is a tool intended to simplify designating relationships between tables and while it may appear similar, it is not a true ERD.

If you placed the "Concepts" table on a MainForm
You can have 2 SubForms, one for each of the intersection tables "C-A_Links" and "C-W_Links"
If you put a table control on each of the SubForms they could use listboxes to select the appropriate values from "Authors", "Works" and "Agreements" tables.

The Form Design Wizard only allows a single SubForm, so you will need to use the Form Navigator in the form Design Mode to add multiple SubForms and perhaps even a SubSubForm.

If you continue to have issues then I would recommend uploading a sample database with your forms so we can see where your problems arise.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Table relationships

Postby sjskhalsa » Mon May 06, 2019 9:38 am

Villeroy wrote:IMHO, this is another many-to-many relation just like the one between Authors and Concepts.
Delete the relation between Authors and Works and run the following in menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Authors_Works"(
  AID INT,
  WID INT,
  PRIMARY KEY(AID,WID),
  FOREIGN KEY (AID) REFERENCES, "Authors" ("ID"),
  FOREIGN KEY (WID) REFERENCES, "Works" ("ID")
);

Then call menu:View>Refresh Tables

P.S. anticipating your question on forms: viewtopic.php?f=13&t=97331&p=466619

Thanks so much for the suggestion. Perhaps there is a typo in the SQL above, because I get:
Code: Select all   Expand viewCollapse view
1: Unexpected token: , in statement [CREATE TABLE "Authors_Works" (
  AID INT,
  WID INT,
  PRIMARY KEY (AID,WID),
  FOREIGN KEY (AID) REFERENCES,]

and I'm not familiar enough with the syntax to spot it. Truth be told, I've only reviewed, and queried relational databases before, never built one. I offered to set this one up for my daughter.
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 5
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby UnklDonald418 » Mon May 06, 2019 4:05 pm

The error message is complaining about the comma following the word REFERENCES. There are two of those that need to be removed. Try:
Code: Select all   Expand viewCollapse view
CREATE TABLE "Authors_Works"(
  AID INT,
  WID INT,
  PRIMARY KEY(AID,WID),
  FOREIGN KEY (AID) REFERENCES "Authors" ("ID"),
  FOREIGN KEY (WID) REFERENCES "Works" ("ID")
);
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1179
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Table relationships

Postby sjskhalsa » Mon May 06, 2019 5:25 pm

Thanks, that executed without error. However, it doesn't look like a new table "Authors_Works" was created, it doesn't appear my list of tables. I'm following the suggestion of the other person who was so kind to comment, and attaching the db. This is a sandbox version just to get the linking tables and forms worked out. The full db has other tables and attributes.
Attachments
Test_linking.odb
(4.49 KiB) Downloaded 24 times
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 5
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby Villeroy » Mon May 06, 2019 7:18 pm

Sorry for the comma.
menu:Tools>Refresh Tables or reload the database document
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Table relationships

Postby sjskhalsa » Mon May 06, 2019 9:00 pm

I realized that the PKs in the authors and works tables were IDAuthor and IDWork, so changed the command to
Code: Select all   Expand viewCollapse view
CREATE TABLE "Authors_Works"(
  AID INT,
  WID INT,
  PRIMARY KEY(AID,WID),
  FOREIGN KEY (AID) REFERENCES "Authors" ("IDAuthor"),
  FOREIGN KEY (WID) REFERENCES "Works" ("IDWork")
);

and now when I View -> Refresh Tables I see the results, a linking table having two primary keys. I will proceed to create the concepts-authors and concepts-works linking tables and see if I can create forms to populate. Thanks again. I will marked solved when I'm all through.
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 5
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby sjskhalsa » Mon May 06, 2019 10:50 pm

The linkage I wanted to make was between concepts and authors and another between concepts and works. so, while
Code: Select all   Expand viewCollapse view
CREATE TABLE "Authors_Concepts" (AID INT, CID INT, PRIMARY KEY (AID,CID), FOREIGN KEY (AID) REFERENCES "Authors" ("IDAuthor"), FOREIGN KEY (CID) REFERENCES "Concepts" ("IDConcept"));
works,
Code: Select all   Expand viewCollapse view
CREATE TABLE "Works_Concepts" (WID INT, CID INT, PRIMARY KEY (WID,CID), FOREIGN KEY (WID) REFERENCES "Works" ("IDWorks"), FOREIGN KEY (CID) REFERENCES "Concepts" ("IDConcept"));
gives
1: Column not found: IDWorks in statement [CREATE TABLE "Works_Concepts" (WID INT, CID INT, PRIMARY KEY (WID,CID), FOREIGN KEY (WID) REFERENCES "Works" ("IDWorks")]
I've removed all other relationships. not sure what's causing the error.
Attachments
Screen Shot 2019-05-06 at 10.50.07 PM.png
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 5
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby Villeroy » Mon May 06, 2019 10:54 pm

The error message says there is no column named "IDWorks". There is one named "IDWork".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26871
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests