More Fun with Libre Office Base

As I wrote last, I’ve had some success moving my submissions from my old self-made system to a database, using LibreOffice Base. I was able to set up database tables for my Titles, Places (Magazines, Book Publishers, Prizes, etc.) and link those with a table for each Submission (one title per submission, though I can see all the submissions I’ve made to each Place when I view the place). So I can view everything I’ve ever submitted, and it all works reasonably well. That’s a huge success. I was even able to design a couple of forms to view each Title and Place with their corresponding Submissions. The problems started when I went to link those databases and write a “form” to enter data.

Here’s the thing about databases that makes them powerful but also takes some getting used to. Each entry in a table needs a Primary Key, which is a unique identifier, usually an integer that is created automatically when you add a new record so that no two recrods have the same primary key. I was able to add primary keys to each of my tables and to cross-reference the existing primary keys for Titles and Places when I brought in my Submissions, but I’m still not used to relying on them, so my databse design is a little wonky. It will be easy enough to fix, but it has taken me awhile to admit that I need to fix it.

I’ve learned that I can create a form with a List Box control that can look up information from one table and store other information from that table. In other words, my list box can allow me to select a title from the Titles table and put the corresponding TitleID primary key in the Submission record, and I can do the same thing for Places. That’s great, but because I’m not used to working with databases, I wanted to store the TitleID, Title, and Genre for each submission, and couldn’t find a way to do that. I could only look up and store one field at a time.

A helpful person on an OpenOffice forum told me I was going about it all wrong, as I was beginning to suspect. (LibreOffice is one implementation of OpenOffice, so they’re essentially the same thing.) I shouldn’t store the Title in both the Submissions and the Titles tables, but should rely on the TitleID and use a databse query to pull the other information from Titles when I read Submissions. Or at least, I think that’s what s/he meant, though I could be explaining it wrong. It gets a little confusing, and I know I have some reading to do to figure out how to use the Query feature, though the helpful person from the forum did send me a couple of examples that are close to what I’ll need (thank you very much!).

I can go back to my Submissions table and delete the fields for Titles, Places, Types (of places), and Genres (of titles). Then a Submission record will just have SubmissionID, PlaceID, TitleID, Date(s), and Response, but none of that pesky text. I’ll have to figure out how to use a query to show me the information I need on certain forms and learn more about subforms and form tables to show the data in the format I want. I have more work to do, in other words, before I can have my database working the way I want, but it can be done, just not the way my non-database brain would have thought.

Incidentally, I’m glad I exported the data the way I did. As I was setting things up, I linked my tables together in Relationships with Primary Keys and Foreign Keys, which is essentially the database terminology for referring to another table’s primary key in a table, and setting them up so that when the primary key gets changed or deleted, that also gets done to the foreign key in the linked table. I got a few errors as I did this and had to search to find out why. As it turned out, some of my Titles didn’t had an empty TitleID field in the Submissions record. Why? I found a couple of misspellings and a few titles whose name had been changed over time. In my old system, the old name could still be out there and wouldn’t be updated. Moving to a relational database should fix that problem. In my Submissions spreadsheet, I could find the empty fields and figure out which TitleID they should have held. Unlike a computer, I can see my old title and remember what the new title is or correct the typo and then look up the right number.

If I hadn’t included the title with each submission when I exported it, then it would have been a lot harder to find and correct the errors with just numbers for identification. But databases work better with limited and accurate information, so the work I’m doing now to clean up my information and set it up correctly will make it all work much more smoothly in the future.

In the process, I may decide to change Genre to Genre and Subgenre and set up two small databases for those, so I can use IDs so I can store those in the database instead of storing text. Then I could have Poetry as genre and Translation as subgenre, for instance. Or Poetry as Genre and Book as Subgenre. I could do the same for Type of place. I could also set up a database of submission Responses to limit those but also make it possible to add options. But we’ll see if I get that ambitious right away.

Now is the best time to do a lot of this, though, since I haven’t started actually using the database yet. But it’s also the start of a new semester, so I have lots of work to do on my syllabi and lots of classes to figure out in my department and students to help sign up for those classes. I can’t devote a whole day to it, in other words, as I could over break. But I can work on it off and on for the next week or so, and I might have a database I’m ready to use and ready to share before too long.