Data Management

DIY: Create easy to use databases with LibreOffice Base

Jack Wallen demonstrates how to use LibreOffice Base on Linux. You can also use the database tool on Windows or Mac.

Many small companies keep their lists of contacts, clients, vendors, and other bits of information nicely tucked within databases. These databases are easy to search, append, and edit. Microsoft Access is usually the tool of choice for such tasks, though it comes with the standard price of Microsoft Office. For businesses on tight budgets, purchasing Microsoft Office (especially for multiple users) is out of the question.

LibreOffice Base is a database tool the average office suite user can actually use. With LibreOffice Base, anyone can create databases and even the front-ends that allow the entering of data. It's simple to set up a database using LibreOffice Base on any platform supported by LibreOffice (Windows, Mac, and Linux). I will demonstrate how to use LibreOffice Base on my platform of choice: Linux.

Requirements

Once you install LibreOffice, you most likely have everything you need; this is especially true on the Windows platform. If you're on the Linux platform, verify the database portion of the suite is installed by opening your Add/Remove Software tool and searching for libreoffice. Make sure libreoffice-base is marked for installation and click Apply (or the Install button) to install the software.

Where to begin

From the main LibreOffice window (Figure A), click the Database icon to fire up the Database Wizard. Figure A

If the database icon is grayed out, it means the database tool is not installed.

Database Wizard

When the Database Wizard opens, the first screen will allow you to create a new database or open an existing database. Make sure that Create A New Database is checked and then click Next.

In the next window (Figure B), make sure to select Yes, Register The Database For Me, so the database is made available to the system. If you do not register the database, the other LibreOffice tools will not be aware of the database. This is important if you plan on importing data from the database into a spreadsheet or text document.

In this same window (Figure B), you are able to set the wizard so it will automatically open the table wizard. I highly recommend you do this because it will open a new wizard where you can begin to set up your database immediately. After you check the Create Tables Using the Table Wizard box, click Finish to begin the process of creating the database tables.

Figure B

When you click Finish, you will be prompted to give the database a filename in order to save.

Table Wizard

The first screen of the Table Wizard (Figure C) allows you to select the fields you want to include in your database table. Before you select the fields, you will want to first select the Category (Business or Personal) for the table; this Category will determine the types of Sample Tables made available to your database. From the Sample tables drop-down, you can select from 22 samples, each containing different collections of fields. You can add as many of those fields as needed by selecting the fields to be included, and then clicking the right-pointing arrow to add them to the table. Figure C

You can re-order the fields to perfectly fit the table you are creating by selecting a field and then clicking the up or down arrow to change its order.
In the next wizard window, the field types and formats can be edited for the table fields. If you go through each field, you will notice the defaults will work in most cases. If a field type or format needs editing, you can make the change in the window shown in Figure D. Note: Use caution with these settings, because you could cause issues down the road. For example, setting a field as Required when it's not really necessary will be a problem when entering data into the table. Figure D

Make sure you edit every field to perfectly suit your needs.

After you make the necessary changes to the field information, click Next to move on.

The next screen (Figure E) allows you to set a primary key, which is necessary in order for each record in a database table to be unique. Unless you really know what you're doing with databases, I highly recommend allowing the software to automatically create the primary key. Figure E

If you're an advanced user, you can modify the primary key or base the primary key on a combination of fields.
The final screen (Figure F) requires you to name your table and to specify what you want to do next. You have three choices: Insert Data Immediately, Modify The Table Design, and Create A Form Based On This Table. Figure F

For the average user to interact with this database, I recommend Create A Form Based On This Table.

If you select the option to Create A Form Based On This Table, when you click Finish, another wizard will appear. We will get into the Form Wizard in our next DIY installment, because that will require a bit of attention to design detail. At this point, you have successfully created a database that can be used for many applications.

Next time, we'll create a user-friendly form that allows for easy data entry into this database. In later posts, we'll focus on connecting or creating a database from within LibreOffice that can connect to MySQL.

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

6 comments
bobp
bobp

Do these steps work equally well with OpenOffice? Also, does OpenOffice work equally well as a front end to MySQL, PostgreSQL, etc.? Thanks.

AllanMitch
AllanMitch

I've tried LibreOffice DB for several projects. If it's just a simple db such as described in the article Base works very nicely. But IMHO the lack of complete documentation and the occasional flackiness of Base just did not lend itself to creating more complex databases and reports.

jlwallen
jlwallen

ricardoc -- that was actually going to be one of my next articles! you read my mind. I'll take that task on asap. Thanks so much for reading DIY! Jack

ricardoc
ricardoc

Hi Jack, Thank you for this post. How about using a form created with the LIbreOffice database that connects to a MySQL database and can be used to input data into the MySQL? Also, can LibreOffice be used to run queries to a connected MySQL database? Thanks,

APSDave
APSDave

There is a connector for MySQL in the create new database wizard. LibreOffice really shines as a front end to several popular database types.

ricardoc
ricardoc

Thanks, APSDave. For some reason my browser was not refreshing the reply to my comment and I could not see yours. I even sent a message to Jack to know whether the post had any issues. I'll have to really try LibreOffice Base to see what it has to offer. Cheers,

Editor's Picks