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: 6
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: 26975
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: 1197
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: 6
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: 1197
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 29 times
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 6
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: 26975
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: 6
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: 6
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: 26975
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Table relationships

Postby sjskhalsa » Tue Aug 20, 2019 9:18 pm

Thank you @Villeroy for all your help. I'm back to trying to get this db set up for my daughter. I've simplified it to two content tables and a linking table. My issue is with creating the form for populating the links table.
Screen Shot 2019-08-20 at 9.00.23 PM.png


I can create forms to accept entries to concepts and sources separately. when it comes to creating the links, I want to form that enable choosing a concept by ConceptShortName and a source by TitleofWork to create the entry in the Source_Concept table, but the wizard allows only one subform. what's the solution?

It'd be great if also when entering a new concept one could pick an existing work and thereby create an entry in the linking table. Likewise to have a form for entering a source where you could pick a concept and thereby create an entry in the linking table.

Thanks in advance for anyone who can help this know-only-enough-to-be-dangerous fella.
OpenOffice 4.1.5 on MacOS 10.14.4
sjskhalsa
 
Posts: 6
Joined: Mon Apr 29, 2019 1:56 am

Re: Table relationships

Postby UnklDonald418 » Tue Aug 20, 2019 11:52 pm

Yes the Form design wizard is quite limited.
For a brief explanation of how to add Forms and Sub forms to an existing Form document see
https://forum.openoffice.org/en/forum/viewtopic.php?f=39&t=98581#p473467
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: 1197
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Table relationships

Postby UnklDonald418 » Wed Aug 21, 2019 4:58 am

I'm assuming you used the Form design wizard and created one form document with the Sources table on the MainForm and the intersection table Source_Concept on the SubForm.
Since there is a one to many relationship you should have used the option to generate a Table control on the SubForm. The result will be a table with 2 columns, SID and CID.
Since SID is linked to IDSource it will automatically use the value of IDSource associated with the selected record in the Source table so, you only need to select a value for CID to make an entry in Source_Concept. That can be accomplished with a List Box.
In the Form Design mode right click on the CID column heading and select Replace with > List Box
Right click again on the CID column Heading, this time select Column to open the Properties: List Box dialog.
On the Data tab change the Type of list contents to Sql.
For List content enter a query
Code: Select all   Expand viewCollapse view
SELECT "ConceptShortName", "IDConcept" FROM "Concepts" ORDER BY "ConceptShortName";

On the General tab of the dialog you might want to increase the Line count to something more than the default value of 5.
Close the dialog and Save the form, then switch to the live mode.
Now you should be able to select a Concept from the list of concepts, press Enter to store the selection in the intersection table.

You could do the same thing with the other form document, but this time the query would be something like
Code: Select all   Expand viewCollapse view
SELECT "TitleofWork", "IDSource" FROM "Sources" ORDER BY "TitleofWork";


You could include both ways of entering data on a single form document, but you would need a way of updating the List Boxes whenever a new record is entered in the Sources table or the Concepts table. That usually involves a couple of Push Button controls with the Action set to Refresh Form. One button on each SubForm.
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: 1197
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 5 guests