Page 1 of 1

Table relationships

PostPosted: Mon Apr 29, 2019 2:15 am
by sjskhalsa
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)

Re: Table relationships

PostPosted: Mon Apr 29, 2019 3:15 pm
by Villeroy
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

Re: Table relationships

PostPosted: Mon Apr 29, 2019 7:17 pm
by UnklDonald418
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.

Re: Table relationships

PostPosted: Mon May 06, 2019 9:38 am
by sjskhalsa
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.

Re: Table relationships

PostPosted: Mon May 06, 2019 4:05 pm
by UnklDonald418
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")
);

Re: Table relationships

PostPosted: Mon May 06, 2019 5:25 pm
by sjskhalsa
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.

Re: Table relationships

PostPosted: Mon May 06, 2019 7:18 pm
by Villeroy
Sorry for the comma.
menu:Tools>Refresh Tables or reload the database document

Re: Table relationships

PostPosted: Mon May 06, 2019 9:00 pm
by sjskhalsa
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.

Re: Table relationships

PostPosted: Mon May 06, 2019 10:50 pm
by sjskhalsa
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.

Re: Table relationships

PostPosted: Mon May 06, 2019 10:54 pm
by Villeroy
The error message says there is no column named "IDWorks". There is one named "IDWork".