Office: Free your Libre Base Open solutions
For those times when spreadsheets just won’t cut it, Bobby Moss can show you a better way to organise your data with some thumping bass.
Just a couple of months ago in LXF231 we covered the finer points of using LibreOfficeCalc to sort, filter and lay out your raw data and calculations in spreadsheets that you can generate reports from. For values that can be nicely laid out in a simple table there’s no better way of extracting meaningful information from your data.
However, there are limitations to that approach. For example, you might be able to fill out a table containing income and expenditure and generate a graph from it, but what happens if the laptop that you bought last year stops working and you can’t remember the purchase date? DuckDuckGo also isn’t going to be able to help you figure out if it’s still under warranty, if you can’t remember which shop you bought it from!
These kinds of situations are where databases excel. By carefully structuring data into tables and then using indexes to link related information together, you can model almost any real-world situation and retrieve data in any number of ways. This can also ensure you aren’t duplicating data because of typographic errors and missing links, which means you can track down that missing warranty information without having to hunt through dozens of spreadsheets and documents.
It’s true that database development might sound like some kind of dark art only seasoned hackers would attempt, but the truth is that with a little knowledge you too can utilise the power that they afford. We’ll cover the basics It’s possible that if you’re reading this you’ve tried Microsoft Access, and this is certainly the best-known personal database application. However, LibreOffice has a very capable free software alternative called Base.
We last covered it in LXF132 before the project forked from OpenOffice.org, and it’s fair to say there have been some changes over the past seven years. Just as with Calc, you’ll notice differences between the two suites, such as slightly different data types and interface differences. We’ll be using LibreOffice because it’s bundled by default with most mainstream distros. However, because Base is an optional part of the office suite you may need to install libreoffice-base through your package manager before proceeding.
When you first start Base you’ll see a wizard pop up offering to create a database using the embedded HSQLDB engine. After calling your database NamesAndAddresses you should click Next and Finish to see the main window.
Down the left panel you’ll see four tabs covering tables, queries, forms and reports. The first two will be where you spend most of your time, because tables will be used to define the structure of your database, while queries will retrieve data from them.
You should begin by creating a couple of sample tables in the new database to store names and addresses. To do so ensure the Tables tab on the left is selected and then click “Create Table in Design View...”. This will open a new window
with a three-column table. These will define what the columns in the table will be called, the kind of data they will contain and a note for what the field should be for.
To begin you should call the first field CustomerID and set the field type to Integer. You’ll notice that in the bottom left corner of the window, new options appear to reflect your chosen data type. This field will be the main index (or Primary key) for this table.
Simply right-click the row in the grey column running down the left-hand side of the window and click Primary Key. When you’re prompted to do so you should save the table as Customers. The next step is to add fields such as firstName and secondName for customer details. It’s good practice to avoid adding spaces to field names, because this can make more advanced tasks such as writing queries using SQL code more difficult.
The next step is to create the Addresses table. Simply follow the same procedure to create your new table, but this time call your primary key AddressID. It may also be sensible to set the Autovalue drop-down to Yes – this will save you having to manually type this in as you add new records.
To complete the table simply add typical address fields such as house number, street name, postal code and so on. Save and close the table.
Joined at the link
So far you’ve created two tables, but you could have done this just as easily in a pair of spreadsheets. The real power of a database is its power to link related data together for easy retrieval later.
For the purposes of this step we’ll assume that each customer can have a work and home address. This gives us a single “one to many” relationship that you can model this with a foreign key. To create this you’ll first need to open the Addresses table in design view and create another CustomerID field. Ensure that it doesn’t autonumber and isn’t set up as a primary key, because it’s important to allow duplicate values.
To link the two tables together you’ll need to open the Relationships... window from the Tools menu. From here you should add the two tables, then click and drag the CustomerID from the Addresses table to the CustomerID field in the Customers table. If the link has been applied successfully then you should see a line appear between the two boxes.
Once you’ve saved and closed the window you can start adding sample data by double-clicking the tables. You’ll notice that if you try to make addresses first, you’ll have error messages popping up, saying that the CustomerID value is invalid. This is because the database relies on being able to look up Customers from its relevant table to check that new data you’re adding is valid, so you’ll need to add customer details first. You’ll also find that you’re able to add more than one person called John Smith because of the unique value you’ve set in the primary key table.
The final step is to differentiate between a work and home address. This is easily accomplished by adding a new string field called Type in the Addresses table design view.
Before we move on to querying, it’s also worth noting that there’s a pair of scenarios this exercise doesn’t account for. What if two customers live in the same household? In addition, what happens if two customers work for the same employer? For those tempted to try and fix those problems you could model these scenarios by adding a table for each that links these customers to their matching addresses.
Query everything
Now that there’s some information in the database, you may find that you want to list all of your customers’ home addresses to send flyers to them. You don’t need all those ID values, but you may need to make sure you don’t include their work addresses. The best way to extract that information is with a query that will output exactly what‘s needed.
To create this head to the Query tab on the left-hand side of the screen and click “Create Query in Design View...”. In the screen that appears you’ll need to add the two tables that you created earlier. From here you can select all the fields that don’t have ID on the end of their name, by clicking each column of the field row and selecting them from the dropdown that appears.
Finally, to ensure that only home addresses are included you’ll need to provide this in the Crierion field for “Type”: ‘Home’
After saving the query you should be able to simply double-click it in the main window to see the results output in a table. You can optionally sort fields either by re-opening the query in design view or by clicking on the various different sort buttons in the window that displays the query’s output.
Light reading
Now we have covered the basics, we can move on to slightly more complex scenarios. Back in the mists of time in LXF132 we organised our DVD collections, but all the cool kids these days use digital downloads and cloud streaming services.
Fortunately, one thing you can’t beat is a good book. It won’t become unreadable in direct sunlight and doesn’t need to be plugged into a wall socket for three hours a day. And of course, taking your book to the side of the pool on holiday isn’t an expensive mistake waiting to happen.
Notwithstanding the many good reasons why you should still buy physical books (and awesome technology magazines about Linux) this is a great situation to use a database. Checking whether you’ve read a book or already own is very useful when you’re planning to order your next tome. You might also be feeling in the mood for a little fantasy, so you could potentially list all the books you own in that genre. Similarly in this age of bulk surveillance and privacy compromises, perhaps you’re tempted to look up what George Orwell’s novel on the subject was called?
The first thing to think about is what makes a book unique. The most obvious field is the title, but that would be a problem for books such as The Lost World, where the title is the same but the authors are completely different (in this case, Michael Crichton’s sequel to his best-selling Jurassic Park, and Sir Arthur Conan Doyle’s tale of a land that time forgot). Clearly, the author of a book is another determining factor, but an author will often write more than one book in their writing career. It seems that we have just defined a “one to many” relationship.
As we mentioned earlier, you might want to filter by genre, so this could be another field that you add to your books. However, a genre will encompass more than one book so there’s potential for duplication. The same could be said about grouping the authors by nationality.
The best place to start would be to create lookup tables to fix the duplication problem. These contain just one field and enable us to check to see if data that has been input in another table is valid using a primary to foreign key join. To do this create two tables, one called Genres and another called Nationalities, each with a single text field called Genre and Nationality. These will be text, but this time you should choose the Varchar_ignorecase data type. Right-click both fields and set them as the primary key for these tables. Once you’re done, save these tables. You should add some sample values to these tables so they can be used for validation later.
The next step is to create an Authors table. Like before, you should create an autonumbering integer field called AuthorID, then add two string fields for first name and second name. Before saving the table also add a text field called Nationality with the same data type you used in the
Nationalities table.
Before you add sample values to the Authors table you should head to Tools>Relationships... and link the two Nationality fields just like you did in the previous example. In this case the Nationality field in Authors should be a foreign key matched to the primary key in the Nationalities table.
Once you’ve saved and closed this and verified that adding some sample author values works with the lookup table, the next step is to create the Books table. In the design view you should simply add an autonumbering BookID field for use as the primary key, the Title as a string field, and then add foreign key fields for AuthorID and Genre. You should be able to link these foreign keys to their respective primary keys just like before in the Relationships... window.
Finally, you should add a Yes/No or “Boolean” type field called Read?. From future table views and forms you should be able to simply check a box to determine if you have read a book or not, but for now you can simply add some sample values for books you have read and are still looking forward to reading.
Pulling it all together
Now that you’ve structured the data it’s possible to create a query that lists all the books you haven’t read yet. Just like before you should head to the Queries tab and create a new query in design view that pulls in all the non-ID fields from the Books and Authors tables. However, by default this will just display every single result.
In order to filter the results you’ll need to alter the Criterion row. To exclude all the books that have already been read simply set the value to 0 under the Read? column. When you save, close and double-click the query. You should now only see records with empty checkboxes in that column.
You can also sort the titles in ascending order by using the Sort row on the relevant column, and you can hide the nationality field by unchecking the correct checkbox Visible? row. If the titles don’t look particularly friendly you can also change how they display by adding an Alias value.
If you wanted to look up all the books by George Orwell you could filter the second name with the following: ‘Orwell’
You can also do some fuzzy case-sensitive filtering with the LIKE keyword and wildcard characters. For example, adding this query under the title field would return any records with the word World in the title: LIKE ‘*World*’
Over to you
There are plenty of ways you could extend the BookCollection database. For example, you can alter the structure so a book can have multiple authors, which is particularly useful if you read a lot of academic texts or non-fiction titles that have co-authors and collaborators. You could also store the synopsis of the plot and your own reviews of novels in Memo fields. These are much larger than standard text fields and massively expand the potential value of searching using SELECT queries.
Similarly, if you added an additional field to the Books table called MoneyPaid with a Decimal data type set to two decimal places, you could use the Function row in the select query to perform calculations, such as adding up how much you’ve spent on books in a particular genre or by a particular author.
If you’re feeling particularly adventurous it’s also possible to store the URL or file links for digital versions of your physical books. You could then use a Macro to fetch the value displayed in the textbox on a form and open it in an ebook reading program.
Finally, for normalisation buffs who are interested in creating a more elegant design, it’s possible to eliminate the ID fields you’ve used as primary keys and replace them with compound primary keys that use a combination of fields in a table to form a unique index.