Base; Setting up relations between tables

users
#1

I haven't worked with SQL DBs since the late 80's so I'm not sure what I'm doing wrong here. I have created a BASE DB using the internal HSQLDB engine for my music collection containing different media (CDs, DVDs, Vinyl LPs, 45s and Cassette, wife had thrown out all my 8-tracks back in '98 as well as my Reel-to-Reel tapes :=( ). I created the main table for Album Info using a field that is auto incremented (essentially record id) integer type, the first field and set as the Primary Key. I then went to create another table (Tracks child of Albums) with TrackID as the first field and Primary key, AlbumID, AlbumTitle, TrackNumber, Track Title, Artist, Time, & Note set as Memo. When I went to set the relations I picked the child table 1st and then I added the parent table using the AlbumID as the field to link. It won't do it. I had it working a few days ago the first time I did this, but when I went to upgrade from Fedora 20 Alpha to Fedora 20 Beta I didn't realize that my backup of the original DBs didn't work so I had to start over. I thought that's what I had done originally when it worked, but apparently not.

So what should I be using to set the relations between child and parent?

Scott C

#2

Scott

Summarizing your two tables:

Album Table
AlbumID - int, primary key
Album
Artist
other Album only data

Track Table
TrackID - int, primary key
AlbumID - foreign key, references row in Album Table
TrackNumber
other Track only data

What you are doing with the graphical tool is set the foreign key
constraint for Track.AlbumID to be the values in Album.AlbumID. The idea
is to limit the valid values to Track.AlbumID to only those in
Album.AlbumID. This is not absolutely needed but can be very convenient
when entering data (152 is a valid AlbumID in Album but 512 is not).

When you query for all tracks for an arbitrary artist using vanilla SQL:

SELECT <fields desired> (each column must be only occur in one table)
FROM Album as a, Track as t (use the correct table names)
WHERE a.Artist = 'some artist'
        AND a.AlbumID = t.AlbumID

In the FROM clause I used aliasing (AS a, AS t) to provide a short
reference to each table.

The SELECT clause must use aliasing (or full referencing) when needed to
make a column unique.

In the WHERE clause aliasing was used to make the references
unambiguous. Also, AlbumID occurs in both tables so aliasing (or full
table referencing) must be used so the query looks for AlbumIDs that
match in each table. Artist probably only exists in Album so alaising or
referencing is optional.

The above query does not require the foreign key constraint be set in
the Track table.

#3

Jay,

So how would I do that in the graphical tool? I can't seem to get it to work. I vaguely remember working with foreign keys and the sql statement would refer to AlbumID from the parent as Album.AlbumID for both tables. Wouldn't I also need the ID from the Album table as well? Maybe I'm just better of using SQL Statements, but I don't see where I can do that. The GUI does not seem to be able to do what I am trying to do or I have forgotten more than I thought.

#4

Hi Scott,

So how would I do that in the graphical tool? I can't seem to get it to
work. I vaguely remember working with foreign keys and the sql statement
would refer to AlbumID from the parent as Album.AlbumID for both tables.
Wouldn't I also need the ID from the Album table as well? Maybe I'm just
better of using SQL Statements, but I don't see where I can do that. The
GUI does not seem to be able to do what I am trying to do or I have
forgotten more than I thought.

If you want to use SQL DDL through LO's UI, Options > SQL (or Tools> SQL
on the Mac) from the main menu is your friend, although the feedback is
minimal (i.e. you get a message that the statement has executed
correctly or not).

Other than that, if you are trying to set it up so that your
Album.AlbumID is referenced in the Track.AlbumID, then the way it should
work in the GUI is via Tools > Relations in the LO Base file main
window, adding the tables you want to join, setting the relation as
1-to-n by dragging the field AlbumID field from your Album table to your
corresponding Track.AlbumID field in the Album table and then setting
the UPDATE/CASCADE/DELTE options as appropriate.

If this doesn't work after saving the database, then that would probably
be a bug.

Alex

#5

Alex,

Within the GUI the steps you describe is exactly the way I had done it. When it didn't work, I was trying several different ways, unfortunately I didn't track what I had changed in all my attempts so I don't remember the alternatives that I tried. I may try using the SQL method as you described to see if maybe something between the GUI and SQL is not happening.
  Thanks for the feed back.

#6

Thanks for the info Jay & Alex. I later went and recreated the db inputting SQL myself and it worked. I then repeated it using the GUI and found my error. I would create the relation by click and drag between tables. Then for some reason I was clicking on the toolbar icon for creating a new relation instead of right clicking on the line to edit the one already created. So I was actually creating duplicate relations. It's working now. I found that I do recall quite a bit of SQL as I also duplicated the full db in SQLite using just SQL, just lack the programming skills to create the UI, and entering the info by SQL statements for each record is a real PITA.

I do have one more question though. I decided to add another table that would allow me to select existing data from this table to fill in a field in the main table. I created the new table with 2 fields. First field is an integer that is auto entered (essentially record count) and the second field is a text field. I then added a field in the main table that provides the link to this new table. In the form I changed the field type from Text Box to Combo Box (originally used List Box, but it didn't work). When I change to entry mode and enter a new record, when I get to this field and I click on the Drop Arrow a blank box is displayed. I am able to manually enter the correct data myself. I do have the new table populated with data.
What am I not doing? I'm thinking of changing one other field this way and was doing this one to figure out doing it.

Scott C.

#7

Hi Scott,

What am I not doing? I'm thinking of changing one other field this way
and was doing this one to figure out doing it.

From your description, it sounds like you have not set the correct bound
field in the properties of your Combobox on your main form.

Enter Design mode for your main form, and then select the control you
want to edit, or if you have both a control and associated label, enter
the group first (right mouse button click on the control in question >
Edit/Enter group). From here, you need to make sure that your box is
bound to the right data source in the Data tab, and the correct field,
or alternatively, make the population of that field dependent on a SQL
statement that basically just says "SELECT * FROM
fieldusedtopopulateform". Save the form, leave edit mode and see if it
now works.

Alex

#8

Alex,

I went back into the design mode and found that I hadn't completely setup the data page in the field control settings. Once I did that when I clicked on the arrow a drop down box appeared with 5 lines from the corresponding table. I assumed it was working fine, so I proceeded to do the same with 2 other fields. I created the tables for them, initially populated them with data, setup the relations with the main table. Again all seemed to be working fine. I was able to either click the arrow for the drop down to list the different values or start typing in the value and it would attempt to complete based on what was in the corresponding table and what I had typed so far.

This afternoon I went back into the db and the main table's form when I noticed that the fields involved were displaying what I had entered for the last record created/edited. If I changed it to correct it I found that for all records that field remained the same. Since so far I've only been entering info from CDs and have not started with any vinyl yet so that field would be the same as well as the genre since I've also been entering in from a collection of the same genre, but the field in question is the one that tracks where I purchased the media from. That field is the one that is supposed to change for at least 2 of the 20 CDs I've entered so far. That was how I noticed that something was wrong. I've also noticed that when I go into Edit mode of the form some fields will display data as though I'm in data entry mode and not design mode. I don't know if this is related or something else happening.

I've tried several different combinations of settings for these fields whit no resolve of problem. On the data page of the field control I have; for Data Fields I have it pointing to the field that has the data I'm using to fill in the main table. for the next 2 settings I have left on default "yes", the type of list contents = Table and List content = name of table that I'm linked to. It seems to me that somehow the relations aren't working the way I want them to. It's applying the same record(s) from the linked table(s) to all records in the main table.

I have the same type of field settings for the primary keys as I do for the main table calling it the table name_ID set as an integer with AutoValue set. I then added on reference of these fields as foreign keys in the main table. The relations were set using the GUI by clicking on the the corresponding fields with the 1 end to the list tables and the N end to the foreign key field in the main table. Maybe it should be the other way or many to many, but I don't see how to do that. It seems that the GUI automatically sets the relation as 1 to N and the directions as well.

Again thanks.

Scott C.