[Solved] Table relationships

[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.

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)

[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

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

[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.

[sjskhalsa]

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

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

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.

[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

CREATE TABLE "Authors_Works"(
  AID INT,
  WID INT,
  PRIMARY KEY(AID,WID),
  FOREIGN KEY (AID) REFERENCES "Authors" ("ID"),
  FOREIGN KEY (WID) REFERENCES "Works" ("ID")
); 

[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.

[Villeroy]

Sorry for the comma.
menu:Tools>Refresh Tables or reload the database document

[sjskhalsa]

I realized that the PKs in the authors and works tables were IDAuthor and IDWork, so changed the command to

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")
);

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.

[sjskhalsa]

The linkage I wanted to make was between concepts and authors and another between concepts and works. so, while

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"));

works,

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"));

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.

[Villeroy]

The error message says there is no column named "IDWorks". There is one named "IDWork".

[sjskhalsa]

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.

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.

[UnklDonald418]

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

[UnklDonald418]

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

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

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.

[sjskhalsa]

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.