Linking two tables

Creating tables and queries

Linking two tables

Postby paladin++ » Tue Mar 22, 2016 2:48 am

I am not new to computers, or to AOO as I have been using it since Star Office. But I am not a database programmer. So now I am trying to write a database system for my new job as an independent trainer. I have a system called training with two tables. Table 1 lists students and table 2 lists course sessions. I want t be able to link the to tables so that a student in table 1 can be registered in course sessions (multiple) in table 2. In short I need a ne (table 1) to many (table 2) relationship and I believe a new joined table but I am not sure how to do this.

Thanks in advance.
Retired Techie
User avatar
paladin++
 
Posts: 7
Joined: Thu May 14, 2015 3:45 am

Re: Linking two tables

Postby FJCC » Tue Mar 22, 2016 4:33 am

I don't think you want a one to many relationship. Each student can be in many courses but each course can probably also have many students. To set up a many to many relationship, you need a third table. Table 1 should have a unique id for each student and this should be the primary key of the table. Table 2 should also have a unique id for each course. The third table should then have two columns, one for a student id and one for a course id. Each row of Table 3 will store a registration. Once the tables are set up, you can create the relationships by going to the menu Tools -> Relationships. Add the tables to the relationship tool and then drag a link from the id of Table 1 to the student id column of Table 3 and also from the course id of Table 2 to the course id column of Table 3.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7791
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Linking two tables

Postby paladin++ » Tue Mar 22, 2016 4:16 pm

oK I understand that and it sounds like just what I need. When I set up table 3 is this a permanent table or a temporary one/ Each table I set up already has an id that is unique to each row, so that's clear, I believe that table 3 should store the 3 id units and student name, class code/class name.

Aside from that I have resisted buying the manuals for HyperSQL (BASE). Since this program is getting involved would it pay to order them, and if so what are the best titles.

Thnaks for the help so far, and the education.
Retired Techie
User avatar
paladin++
 
Posts: 7
Joined: Thu May 14, 2015 3:45 am

Re: Linking two tables

Postby FJCC » Tue Mar 22, 2016 5:08 pm

The third table is permanent and should contain only the id numbers of the students and courses, not the student names or course names. Information should be stored in only one place and the relationship between the student id and the name will be in Table 1. One advantage of that is if a student name or course name changes, only one record will have to be edited. When you run queries, you can use table joins to retrieve the names associated with id numbers.

Documentation for the HSQLDB version that ships with Open office is available here.. There are much more recent versions of HSQLDB which can be run independently of OpenOffice. That is a much safer way to set up the database. See this tutorial on avoiding data loss..

A good tutorial on Base and the basics of database design is here.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7791
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 0 guests