Create a good Base for your data

Databases are everywhere. Withdraw money from a cash machine or log into Facebook and there’s a database in the background. There’s nothing magical about them. A database organises information within a planned structure. This could be a card-file index or the huge electronic databases that power Google.

The closest most of us get to managing databases is making lists in a spreadsheet application such as Microsoft Excel. That’s fine for the most part but, with a bit more effort, you can benefit in a big way from databases.

To give an idea of how powerful databases can be, we’re going to use the free Libre Office Base application to keep track of books and CDs loaned to friends. More importantly, it will also record when items are lent out, when they are expected back and whether the deadline has been met.

It’s not a guide for complete computer novices but, if you’re reasonably confident using the likes of Excel, then you should get on just fine.

What is a database?
A good database organises data in a particular way to avoid repeating information and is very flexible when creating reports. Modern databases even have their own programming languages but we won’t concern ourselves with any of that here – as Base will do the hard work.

Most databases are made of grid-like tables that contain different but related sets of information. Each row in the table is called a record and each cell within the record is called a field. In our database, for example, we’re going to create a record for each friend, and each record will contain fields to store their name and telephone number.

You might think it’s possible to achieve much the same in Microsoft Excel, by creating a workbook with several worksheet tabs. However, Base is able to link its tables together in a way that’s much more useful – to create what’s known as a relational database.

We want to include the contact details of the people to whom we have lent stuff. A list in Excel would require those contact details to be added to every loan record. By contrast, our database requires only one contact details entry per person. This both reduces time spent entering information and, if someone gets a new mobile phone, say, then the information will need to be updated just once.

Base can also be set to be far stricter about how information is typed in. If a field is set to contain a date, for instance, Base will prevent anything else from being entered.

Downloading and installing Libre Office
Libre Office is the new name for the Open Office suite, a popular free alternative to Microsoft Office. It is a complete office suite that comes with a word processor, spreadsheet program and more. It also includes Base, the database application that we’re going to use.

Download Libre Office here and the Java Runtime Environment is also required (your PC may have this installed but we would advise downloading and installing the latest version). Once the files have downloaded to your PC, double-click them and and follow the respective installation wizards.

Creating the database
Launch Libre Office Base from the Start menu and the Database Wizard will appear. The first choice is whether to start a new database, open an existing database or connect to an existing database. Leave Create a New database selected and click Next. Leave the ‘Yes, register the database for me’ and ‘Open the database for editing’ options selected. Click Finish. Now select a location and name for the database and click Save.

Planning
Deciding how to arrange the tables is the first step when creating a database. Tables should be planned to avoid repeating information that could be kept in another table.

 

This concept is probably easiest to understand by looking at the screenshot above that shows the relationships between the different tables. Ignore the lines between the tables for the moment and look at what the tables contain.

The first table is called MyStuff. It contains information about the items that are loaned. However, information about people and the loans themselves are stored in different tables (called People and Loans in our example).

The fourth table, called ItemType, will be used to create a dropdown menu when items are added. It will work like a validated list in Excel, where the list of options is limited to a specified range. However, Base makes this easier to change as more items can be added without having to change the range used to validate the cell or cells.

The purpose of primary keys
Every record in a table requires a unique name to identify it. In Excel you might use the row number but databases are more flexible as they can use any kind of data so long as it is unique.

This identifying field is known as the ‘primary key’: in our tables, these are marked with a key (on the MyStuff table, for instance, the primary key is ItemName – which is a field that will contain the name of the item).

For the People table, we have made Name the primary key: this should be fine, unless you happen to have friends that share the same name. If you do, you will need to tweak one of them to make the name unique (by adding a middle initial, for instance).

Making the first table
To start, click on the Tables icon in the left-hand column and then Create Table in Design View. Type ItemName in the Field name cell and leave the field type as Text [VARCHAR]. Right-click on the green arrow on the left of the Field Name and left-click Primary Key. Click in the row below, type ItemType and press the Tab key to move to the next cell. Leave this as Text [VARCHAR].

Repeat this for the next two rows that should be named ItemDescription and CurrentOwner. The Description field can be used to include more information: it is a good idea to include as much information about the field as you can, as later the purpose of fields may seem less than obvious.

There are more options about the field at the bottom of this window. It is possible to set whether or not there must be information entered in a field; and whether a default entry should be made if the user doesn’t make a choice. The default entry for the CurrentOwner field, for example, could be set as Me rather than leaving it blank. Click on the Save (floppy disk) icon in the toolbar and enter the name of the table. This table should be called MyStuff.

A table for list entry
The next table is even simpler. Click on Create Table in Design View option again. Name the first field ItemType and leave the Field Type as Text [VARCHAR]. Make this the primary key and save the table with the name ItemType. This table will be used to create a dropdown menu for filling in the ItemType field in the MyStuff table.

The People table
The third table is also simple to create. There are four fields: Name, EmailAddress, Landline and MobileNumber. All should be of Field Type Text [VARCHAR] and Name should be set as the primary key. Save this table with the name People.

Recording loans
The final table, used to record the loans, is more sophisticated. The first field should be called ID and the Field Type set to Integer [INTEGER]. Set this as the primary key and then select Yes in the option AutoValue at the bottom of the table creation. Numbers are an easy way to identify loans and this avoids the hassle of trying to remember the next number to use.

The next two fields, Item and LentToPerson are text fields. The fourth field should be called Returned and set as type Yes/No [BOOLEAN]. A field called Notes follows as Text [VARCHAR] type. The final two fields, DateLent and DateExpected should both be set to Date [DATE]. This ensures that we can create reports based on overdue items. Save this table as Loans.

Family ties
The next stage is to tell Base how all these tables relate to each other – to create our relational database. Click on Tools and then Relationships.

A window will appear with a list of all the tables. Click on each one and then Add in turn to add them to the Relationships window. Click and drag the tables by their title bars to arrange them on the window.

Creating a relationship between two tables is as simple as clicking and dragging from a field in one table to another. Let’s start with the ItemType fields. Click and drag ItemType in the ItemType table to ItemType in MyStuff.

A line should appear with a ‘1’ at one end and an ‘n’ at another. This refers to the specific kind of relationship but that’s an advanced topic we won’t be covering and doesn’t matter for our purposes. Click and drag to create links between the following tables and fields: MyStuff.CurrentOwner and People.Name, MyStuff.ItemName and Loans.Item, and People.Name and Loans.LentToPerson. Click on Save and close this window.

Adding information
It is possible to type information directly into tables. Double-click on the ItemType table to edit the data. Click in the first (and only) column, type Book and press tab to move to the next record. Repeat this for as many types of items as you want. For example: Book, CD, DVD, LP and WiiGame. Click on the Save button and close this window. You can also fill in the People table in the same way.

Fabulous forms
To include information for more than one table a form needs to be created. Click the Forms icon on the left followed by Use Wizard to Create Form. Select the option Table:MyStuff from the dropdown menu and click the double-chevron (‘>>’) icon to use all the fields in your form. Click Next twice.

Choose the left-hand button for the Arrangement of the main form and click on Next three times. Give the form a name, MyStuff, select Modify the form and click on Finish. The form is created with text boxes for the different fields.

This form doesn’t use the ItemType table yet. Hold down the Control (Ctrl) button and click on the Item Type text box so that it is selected. Press the Delete (Del) key to remove it. Now click on the Listbox icon in the left-hand toolbar (it should be the 10th icon down). Click and drag to draw a box where the text box was and release the mouse – a wizard will appear. Click ItemType and then Next.

Repeat in the next step. Select ItemType in both sides of the next step and click Finish. This dropdown menu will offer the options from the ItemType table and insert the option you select in the MyStuff table. Click on Save and close the window.

Recording loans
The form for adding a loan is similar. Click the Use Wizard to Create form option and include all the fields from the Table:Loans option. Repeat all the options for the last table, select Modify the form option and click Finish. Notice that the Returned option is automatically turned into a tick box. Delete the text boxes for Item and LentToPerson. Replace them with Listboxes.

For Item, choose the MyStuff table then ItemName. Choose Item and ItemName in the next window and click on Finish. For LentToPerson, choose the People table and then Name. Choose LentToPerson and Name, and then click on Finish. Save and close the form.

You can now enter information in both forms by double-clicking on the form name in the Forms section of the main window.

Queries
Now that we have some information in the database, it’s time to do something with it. The most important question with our loans database is what items are overdue. We will do this using the database language SQL, which might sound rather scary. However, as you will see, it is quite readable.

 

Click Queries and then Create Query in SQL view. Click in the main text box and type the following exactly as it appears including capitals and commas:

SELECT "Item" AS "Item", "LentToPerson" AS "LentToPerson", "Returned" AS "Returned", "DateExpected" AS "DateExpected" FROM "Loans" WHERE "Returned" = 0 AND "DateExpected" < CURRENT_DATE

Click on the Save button and call the Query OutstandingLoans. If you read that query from left to right a few times, it should begin to make sense. Press F5 on your keyboard to run the query and see the results. As you can see it displays every loan that has not been returned where the expected return date is before the current date. Close this window.

Adding extra information
This query is very helpful but it would be even better if it could include the contact details of the people with outstanding loans. We can do this by adding information from the People table. Right-click on the query and left-click on Edit. Click on Insert and then Add Table or Query. Click on People followed by Add and then Close.

Click in the first empty field column and select People.LandLine from the dropdown menu. Click the Save button and then press F5 to run the query and see the telephone numbers of the people with outstanding items.

Reports
All this data is useful but not very presentable. That’s where Base’s Reports feature comes in. Click Reports and then choose Use Wizard to Create Report. Select Query.OutstandingLoans in the dropdown menu and click on the double-chevron (‘>>’) button to copy all the fields to the report. In fact, we don’t need the Returned field as the report will only include outstanding loans.

Click on it once and then on the left-facing single-chevron (‘’) so that all an individual’s outstanding loans are grouped together. Click on Finish to see the report in Libre Office Writer and then format it as desired before printing it out.

Note that the date field will appear as a number. To change this, right-click the report and select Edit. Right-click the number underneath DateExpected and select Number Format. Select Date on the left and choose a format. Click OK.

Just the start
Hopefully we have shown how powerful databases are. We’ve barely scratched the surface of what is possible but they can revolutionise what can be done with data.

Our example database can handle much larger lists than would be possible in Excel and presents the information in a way that is easier to read. If you want to get a headstart, our sample database can be downloaded here. Now it’s up to you to expand or customise it.