[Solved] Linking multiple columns to a single table

[nzeed]

I'm trying to work out how to go about linking data in two tables. This should explain the logic:

tbl_Episodes

tbl_Crew

tbl_Crew.jpg (21.76 KiB) Viewed 2944 times

(I tried to do this in tables but following some trial and error and a forum search it seems that you can't do multiple-column tables in bbCode on this forum)

Who.odb

(16.69 KiB) Downloaded 169 times

The main issues are:

* More than on one person can have a particular role (e.g. an episode can have more than one Writer)
* A person can have multiple roles (e.g. the Writer of one episode could be the Script Editor of another)

You can't create more than one link to the same table (I tried creating separate ID values for each of the roles and linking those but that didn't work). Attempting to create a 'standard' many-to-many link table complains of 'Primary or unique contraint'.

I assume, if this is possible, I'll need some kind of many-to-many relationship but I'm not sure how to go about it.

I'd ultimately like to be able to use this to diplay the episodes that each person worked on and in what role.

Any advice would be appreciated.

Thanks!

[UnklDonald418]

Database design is not intuitive. It takes considerable knowledge and experience to come up with something more than a rudimentary table and form. Have you read any books on the subject?
There is a fairly comprehensive tutorial that I found helpful and still refer to on occasion. It can be downloaded at
https://wiki.documentfoundation.org/ima ... torial.pdf

You will definitely need more than 2 tables. Just giving it a quick overview, I would recommend one table for Persons, it may just be a list of names with an associated ID or it may contain other personal information. The Episodes table which you already have will probably be inadequate. For instance because there can multiple Writers you would need a separate table for Writers, etc.

I'd ultimately like to be able to use this to diplay the episodes that each person worked on and in what role.

Maybe a Role table would be your main focus with another table linking Roles to Persons and still another linking Roles to Episodes.

It often takes multiple attempts to get a working design. Knowledge, experience and a little luck help minimize the number of false starts.

Good Luck

[MTP]

The Base GUI is not able to handle this task, but the underlying HSQLDB (Hyper-SQL DataBase) is OK with it.

To bypass the GUI and talk to the backend HSQLDB, open the Tools→SQL window and then enter these lines:

Code: Select all

ALTER TABLE "tbl_Episodes" ADD CONSTRAINT "FK_Writer" FOREIGN KEY ("Writer") REFERENCES "tbl_Crew" ("ID");
ALTER TABLE "tbl_Episodes" ADD CONSTRAINT "FK_Director" FOREIGN KEY ("Director") REFERENCES "tbl_Crew" ("ID");
ALTER TABLE "tbl_Episodes" ADD CONSTRAINT "FK_Script_Editor" FOREIGN KEY ("Script Editor") REFERENCES "tbl_Crew" ("ID");
ALTER TABLE "tbl_Episodes" ADD CONSTRAINT "FK_Producer" FOREIGN KEY ("Producer") REFERENCES "tbl_Crew" ("ID");
ALTER TABLE "tbl_Episodes" ADD CONSTRAINT "FK_Executive_Producer" FOREIGN KEY ("Executive Producer") REFERENCES "tbl_Crew" ("ID");

Execute that code, then close the SQL window. Next choose View→Refresh Tables. Now you can open the relationships window and see the correct relationships exist.

That will fix the people having different roles in different episodes. I agree with UnklDonald418 on the multiple people for a single role situation requiring a separate join table be created.

[nzeed]

Thank you both for your excellent advice. I've made some good progress based on this.

As you can tell, I'm am quite new to all this. I have some experience in maintaining existing databases but creating a new database from scratch is not something I've done before. I went for Dr. Who because the dataset is quite complex so I thought it'd be a good opportunity to learn about database design. I'm not a total Dr. Who geek, honest! As you say, trial and error is a big part of this learning experience and I have found this already.

Thanks for the link to the tutorial. I'm not great at reading large documents on-screen so I'm going to try and get a copy printed (the cheapest print and bind service I've found so far is doxdirect.com but if anyone knows any cheaper UK services please let me know). I've been going through the FrugalComputerGuy vids on YouTube, which are brilliant.

I think now I was trying to overcomplicate matters by having separate columns in the episodes table for the different roles and have gone for a structure more similar to that of my previous post. I've created a linking table that has links to both the tbl_Crew table and a new tbl_Role table.

I've added some dummy data into the linking table and created a query to confirm that this is all linking and displaying OK.

Obviously when adding to the linking table I have to cross-reference the other two tables for the IDs, so I'm now trying to create a form so I can add this data more intuitively. I'm trying to do this with a list-box, so I can select the Role from tbl_Role and the Name from tbl_Crew. I'm struggling with this bit now, so any further advice will be greatly appreciated. If I get any further before a reply is forthcoming I'll post an update.

Thanks again.

Who2.odb

(60.93 KiB) Downloaded 133 times

[UnklDonald418]

In order to get list boxes to display the information stored in linked tables you need to use queries.
Open your form in the edit mode, and right click the Role column and select Column to open the Properties: List box dialog, and select the Data tab. There are a couple of options here. You could use a pre-defined Query as the Type of list content, or as I did in the example shown here use Sql and List Content=

Code: Select all

SELECT "Role", "ID" FROM "tbl_Role" ORDER BY "Role" ASC;

If you choose Query then List Content would contain the name of the Query otherwise use what I show.

Untitled.png (6.39 KiB) Viewed 2870 times

The advantage of using the Sql command is that if at some time in the future someone were to modify the Query it wouldn't affect how your form works.
You have to keep in mind that numbering in Base begins with 0 not 1, so by placing “Role” in the first position (0) it will be what will be displayed in the list box. With “ID” in the second position (1) it will be what is actually stored, because is is the Bound field. The Bound field defaults to 1 and although you could change it I wouldn't recommend doing that.
Likewise for the name column you would use SQL

Code: Select all

SELECT "Name", "ID" FROM "tbl_Crew" ORDER BY "Name" ASC;

Also notice that each of the queries ends with an ORDER BY xx ASC statement. This simply sorts them in alphabetical order making it easier to find your selection in the list. Notice that if you press a letter key the pointer will jump to the first entry in your list beginning with that letter, which helps speed up the selection process. List boxes only allow a single character so if you press A followed by N the pointer will jump to the first entry beginning with N. A combo box, on the other hand would jump to the first entry beginning with AN.
You might consider splitting your names into first and last names. Split names would require slightly more complex queries.

[nzeed]

Perfect, thank you. And for the explaination aswell, that's really useful.