Creating database tables with LibreOffice Base wizards

LibreOffice Wizards facilitate the creation of databasess. They include the main types that we may need and it is easy to adapt them.

In our previous article We had briefly explained what the components of a database were; tables, forms, queries and reports. With the help of LibreOffice Base we are going to see how they behave in a real example. In this post we are going to deal with the tables.

Tables are the foundation of a database. They consist of a series of related data grouped according to their characteristics in fields. For example, in the Clients database we have the Agenda table and the Name, address, telephone and mail fields.

Before continuing, a clarification. I am not writing a monograph on relational databases. I only include enough theory so that any user can begin to enjoy the program. Anyway, any comment you want to make on the subject, there you have the comment form.

Creating our first database with LibreOffice Base wizards

In this article we are going to focus on creating database tables. We will do it using one of the internal engines of the program. LibreOffice Base allows us to choose between Firebird and HSQLDB.

Neither in the LibreOffice Base documentation (which remained in version 4) nor in the most current Getting Started Guide (version 6) Firebird is mentioned. For our modest purposes, there seems to be no major difference between the two so we are going to use Firebird which is the default option.

Our first step, once the program has started, is choose the database engine. We stay with Firebird.

Click on Next to go to the second screen

On the second screen we are given the possibility of register the database. The difference between doing it or not is that when you register it, we tell LibreOffice where the data is, how they are organized and how they can be obtained. In this way we can access data records from word processor and spreadsheet.

We mark the option to open the database and create tables through the wizard. LibreOffice will ask us to name and save the database.

The table wizard is divided into two sections; Business and private. For each of them it offers us a series of templates. The templates in turn show us a list of fields.

As an example, we are going to use the asset template from the Business section.

In the central column we see a single and a double arrow that go to the left and the same to the right. They they control the offset of fields between the two columns. The fields that appear in the right column are the ones that will make up our table. If we press the double arrows, all the fields will be selected.

In my case, I select ActiveID as the first field. For this I select it with the pointer and press the simple arrow that points to the right. I repeat the procedure with all the fields that interest me.

Once this is finished we go to the next screen. Here we are going to decide if we keep the names of the fields or assign them others. Also, we establish some parameters on data entry. They are:

• Format of the data entered.
• If a value is assigned automatically. If you activate this option, you must also add the increment value from input to input.
• If it is mandatory or not to enter the data.
• Maximum length of the entered data.

To the ActiveID field I assign the integer format and do not select the option to complete automatically. This field will have the function of being the primary key. I will explain this below.

For brand and model I assign them the format of fixed text type and mandatory entry. I assign a variable field type to description since I may need more than the 255 characters that the other option has as a limit.

Assigning a primary key

Within a database there can be many tables with multiple records. These records are in turn used to create other tables. To facilitate your identification, LibreOffice Base assigns you a code or primary key.

I choose to use the ActiveID parameter as the primary key. You had previously selected that the value of this field be filled in automatically. However, that causes an error message that I cannot identify, so I disabled this option. Don't do it in this window either.

The use of the primary key is useful for example if we have two assets of the same brand and model

Once we assign the primary key we are ready to start with the next step, assign the table name and start filling in the data. But we will see that in the next post.