[Solved] Table relationships
[Solved] Table relationships
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.
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)
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)
Last edited by sjskhalsa on Thu Aug 22, 2019 11:21 am, edited 1 time in total.
OpenOffice 4.1.5 on MacOS 10.14.4
Re: Table relationships
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...
Then call menu:View>Refresh Tables
P.S. anticipating your question on forms: viewtopic.php?f=13&t=97331&p=466619
Delete the relation between Authors and Works and run the following in menu:Tools>SQL...
Code: Select all
CREATE TABLE "Authors_Works"(
AID INT,
WID INT,
PRIMARY KEY(AID,WID),
FOREIGN KEY (AID) REFERENCES, "Authors" ("ID"),
FOREIGN KEY (WID) REFERENCES, "Works" ("ID")
);
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Table relationships
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 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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Table relationships
Thanks so much for the suggestion. Perhaps there is a typo in the SQL above, because I get: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...Then call menu:View>Refresh TablesCode: Select all
CREATE TABLE "Authors_Works"( AID INT, WID INT, PRIMARY KEY(AID,WID), FOREIGN KEY (AID) REFERENCES, "Authors" ("ID"), FOREIGN KEY (WID) REFERENCES, "Works" ("ID") );
P.S. anticipating your question on forms: viewtopic.php?f=13&t=97331&p=466619
Code: Select all
1: Unexpected token: , in statement [CREATE TABLE "Authors_Works" (
AID INT,
WID INT,
PRIMARY KEY (AID,WID),
FOREIGN KEY (AID) REFERENCES,]
OpenOffice 4.1.5 on MacOS 10.14.4
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Table relationships
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
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Table relationships
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 282 times
OpenOffice 4.1.5 on MacOS 10.14.4
Re: Table relationships
Sorry for the comma.
menu:Tools>Refresh Tables or reload the database document
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Table relationships
I realized that the PKs in the authors and works tables were IDAuthor and IDWork, so changed the command to
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.
Code: Select all
CREATE TABLE "Authors_Works"(
AID INT,
WID INT,
PRIMARY KEY(AID,WID),
FOREIGN KEY (AID) REFERENCES "Authors" ("IDAuthor"),
FOREIGN KEY (WID) REFERENCES "Works" ("IDWork")
);
OpenOffice 4.1.5 on MacOS 10.14.4
Re: Table relationships
The linkage I wanted to make was between concepts and authors and another between concepts and works. so, while works,
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.
Code: Select all
CREATE TABLE "Authors_Concepts" (AID INT, CID INT, PRIMARY KEY (AID,CID), FOREIGN KEY (AID) REFERENCES "Authors" ("IDAuthor"), FOREIGN KEY (CID) REFERENCES "Concepts" ("IDConcept"));
Code: Select all
CREATE TABLE "Works_Concepts" (WID INT, CID INT, PRIMARY KEY (WID,CID), FOREIGN KEY (WID) REFERENCES "Works" ("IDWorks"), FOREIGN KEY (CID) REFERENCES "Concepts" ("IDConcept"));
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.
OpenOffice 4.1.5 on MacOS 10.14.4
Re: Table relationships
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Table relationships
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.
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.
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
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Table relationships
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
viewtopic.php?f=39&t=98581#p473467
For a brief explanation of how to add Forms and Sub forms to an existing Form document see
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Table relationships
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
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
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.
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
SELECT "ConceptShortName", "IDConcept" FROM "Concepts" ORDER BY "ConceptShortName";
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
SELECT "TitleofWork", "IDSource" FROM "Sources" ORDER BY "TitleofWork";
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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: [Solved] Table relationships
Bingo. I now have the working database and forms that I sought. I have learned a lot through this crash course. I am very grateful that I was able to tap into the knowledge of the community.
OpenOffice 4.1.5 on MacOS 10.14.4