Monthly Archives: July 2011

first Openoffice database (part 2)

part 2 concerning open office database mailing address example continued from part 1…     

In the prior post we describe how to register the proposed address database.   We learned for situations that do not require forms, for example, if you want to only use tables, the best choice would be to NOT to register. However if you think you might want to create forms that link to the database tables, you would want to register the openoffice database. 

So, in summary, if you expect to create forms to help users enter data, then, you should Register the openoffice database.

How to Register an existing Open Office database

If you have an existing database, first Choose Tools, Options. Under OpenOffice.org Databases, Click New and select the database File.

How To Remove a registered open office database from openoffice.org?

First, in the menu, Choose Tools, Options and select OpenOffice.org Base Database.
Then select the database file. Finally Click Delete.

OpenOffice Help System and User Interface

One thing I like about Open Office is the clean and fresh look that have in the user interface and help system. Simple to use, Openoffice.org’s help system is easy to use and has nice fresh look to it.     

QuickStarter: Opening OpenOffice.org from the Windows System Tray

If you installed OpenOffice 3 you might notice on a client with the windows operating sytem, the open officeoffice.org 3.3 QuickStarter. This service runs in the system tray area. If you double click on it, a different entry point to the program pops up. Wehn you do this, the “Templates and Documents – New Document” user interface opens.

If you want a new database, just click on Database. YOu are taken then to the familiar open office Base database wizard.

What if you want to open an existing document in via quick starter?

I found the steps to do this is a little Counter intuitive. That said, you want to do this. Make sure the the left hand side column item ‘New Document’ as selected. As before, choose database. Under Step 1, “What do you want to do”, select “open an existing database fiel”. In the recently used drop down, find the database.

While on the Save and Proceed Step ,click on ‘Finish’. When you do so, a file save operation begins and you must choose the file name.    

  

openoffice save and proceed

openoffice save and proceed

How to create a Persons table for our openoffice address database   

Next choose Create Table in Design View.

creating an openoffice address database 

 
 
Next, Choose some fields to add in. Here is an example screenshot.
Creating a Person table in openoffice.org
Creating a Person table in openoffice.org

    

Openoffice.org Auto Value

On thing that I found interesting is choosing an autonumber on the PersonID resulted in OpenOffice.org automatically assigning a key icon to that column. It appears to know that we want to make it a primary key. I sort of like this, but it was a little unexpected.      

Below the Field Name and Field Type grid, is the area where you can choose different options. Among those is the ability to choose an autonumber field when the openoffice database field type is an integer.      

Open Office Org's Auto Value (aka Auto Number)

Open Office Org's Auto Value (aka Auto Number)

I call this option auto number, but you could call it a sequence or incrementing column as well. In any case if you choose Autovalue, the number will automatically increment.  Also openoffice.org can make this field a primary key automatically.      

Another thing I noticed: I wasn’t able to drag up or down the rows to order them the way I wanted. However I could accomplish the same thing if I did a ‘cut and paste’ operation.       

Next, let’s create another table for the addresses. This table we shall call ‘Address’. Just like Persons we make AddressId, an ‘Auto Value’ and that turns it into a primary key.      

openoffice address table in edit mode 
Above: openoffice address table in edit mode

Next, create a new table to serve as a ‘junction table’ between Persons and Addresses. I called it PersonAddress table.      

Person Address Table

Person Address Table functions as a cross reference table

       

       

       

To ensure uniqueness on the row I needed to create a unique index.  Click on the small yellow icon right above the field name column of the grid. It brings up a form to set an index.   

Person Address Index
Person Address Index

Under the menu command Tools…Relationships bring up the “Base: Relation Design” window.  

open office tools relationships window where you can set foreign key relationships
open office tools relationships window where you can set foreign key relationships

Here simply drag the primary keys over to the cross-reference table. Your design should look like this…

We’ve created a simple openoffice database which includes entities, attributes, indexes, and foreign keys. Openoffice.org is a free tool anyone can download and use. In particular, the free open office database known as ‘Base’ can be very useful and friendly to use. The above open office database was quickly created in minutes. Using an example of a mailing address database, the open office database proved itself useful and fun to try out.