Creating a join with a composite key

[ugobananas]

It's been almost a decade since I've touched anything resembling db design and even longer since I've used a DBMS like Base or Access, and I've just started a personal project using OO Base.

I have a table called Episodes that I want to join with a table called Characters; since there can be multiple characters in multiple episodes, I've also created a table called CharEp to join the two. The primary key in Episodes is a composite key of Seasons and EpNum (two, 2-character fields that always result in a unique string). The primary key in Characters (CharID) is autovalued. CharEp should join the two; right now I've created two fields in that table: ChID and EpID where EpID should contain the composite key from Episodes.



In my mind what I want to do is join the tables something like this:

Code: Select all

INNER JOIN CharEp
ON Episodes.Season + Episodes.EpNum = CharEp.EpID

but I don't actually know HOW to achieve that in OO.

I suspect that I might just be approaching the whole setup wrong, so here I am asking for any help for the best way to set up the relationship between the two tables, Characters and Episodes; nothing is populated yet at this point so I can scrap and rework anything without any harm. Any input is greatly appreciated, thanks in advance!

[Villeroy]

|| Is the concatenation operator.

Code: Select all

INNER JOIN "CharEp"
ON "Episodes"."Season" || "Episodes"."EpNum" = "CharEp"."EpID"

The embedded HSQL is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Also relevant: http://www.hsqldb.org/doc/1.8/guide/ch06.html

[Nick N,]

UgoBananas

Hi,

Mr. Villeroy's proposal is one of the multiple solutions offered to you.

You were talking about Manitou-Manitou tables relationship: in reply to above please find screenshot below.

Of course, at the time you build relationship, do not forget to select the CASCADE option on the left.

Regards

Nick