Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgYou may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.
Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about ooBase in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software ®, 2/06-5/09.
In the first tutorial in this series, I suggest that you use the wizard to create your first table. Perhaps that is best for a beginner.... but it really isn't necessary most of the time. (More on when it is necessary in a moment. In some other tutorials, the suggestion to use the wizard is a hangover from my early, ignorant, days.)
First I need to be sure you are clear about something. I have not made it entirely clear previously. And of course, you may not have read some of my earlier tutorials. In any case, the point bears repeating. The point applies to all flavors of SQL. ooBase is, essentially, a front end giving you access to many database engines. I hope you are using it to access the HSQL engine which comes with Open Office... that leaves several fewer things to "go wrong". If so, you are working in a SQL environment.
When working with SQL, when you speak of "a database", you are sometimes referring to something akin to a container. A SQL "database" contains one or more tables, plus zero or more forms, queries and reports. (The term gets used in other ways, too, but the "container" meaning is an important one.) That container does a number of things, one of which is to "hold" all of the bits of the database in a single file. You don't get a separate file for each table, form, query, report.
When working with SQL, you first create the database, i.e. the "container". Initially, it doesn't contain even a table. (It is pretty useless like this... it is just the container, which is ready to acquire a table, and thus the beginnings of its usefulness.)
Creating the database with ooBase is easy, and is explicitly explained, in detail, in all of my introductory tutorials.
The second step of the ooBase "new database" wizard asks you to select either "Open the database for editing" or "Create tables using the table wizard" as what you do after the wizard completes. I was confused by this terminology. It isn't wrong, but it mislead me.
When you run the New Database Wizard, the database gets opened regardless of which option you choose. If you choose the first, you are not electing to edit data that you have stored in some table... there are no tables yet.
If you just "open the database", you will find yourself looking at the main ooBase project management window, but there will not yet be any tables, forms, queries or reports. Your first task will be to create at least one table.
If you elect to "create table....", you will also open the database. You can't work on any part of it unless it is open. And until it has a table, there's nothing to work on. The "create table...." option really ought to be called "Open database and go straight into creating a table, using the wizard."
Good news! Things start getting easier now!
A moment ago, I said "Your first task will be to create at least one table." There are always two ways to create a table...
There is no difference between the resulting tables.
Elsewhere, I often suggest that you invoke the "... using table wizard" option. The more I use ooBase, the more I prefer the alternative.... create the database, and then use "Open database for editing" at the end of creating the database (the "container"). If you do this, you are taken to the ooBase project management window, the one with three panes: "Database", "Tasks", and a third pane which is "Tables", "Queries" "Forms" or "Reports", depending on what you have selected in the "Database" pane. It looks the way things look when you re-open a database you've done some work with. Except, this first time, there are no tables, forms, queries or reports yet. Click on "Tables" and then "Create new table in design view".
There's not much to tell you. Invoke that option, and you will be presented with a datasheet display, i.e. a display that looks very like a spreadsheet. There are multiple rows, three columns. At the bottom of the page there's an area where you can (eventually) set properties.
If your table needs four fields in each record, you will fill in four rows of the datasheet. Don't let this disconcert you. Yes... fields are usually the columns of a datasheet. Don't let the layout in the Table Design window confuse you. The Table Design window is not showing a datasheet view of a table (rows of records / columns of fields). It is showing the tables fields, one field to a row. The columns are pretty self explanatory, I hope, but let's go through them....
The first column is for is the field name, which can be anything reasonable. I don't think you allowed spaces in the name. It would be best too avoid them, in any case. The names can be case sensitive. (I.e., if you call the field "Address", you can't subsequently refer to it as "address", ADDRESS or "aDDRESS".) I said "can be case sensitive". The rules for when they are and when they aren't are complex. Make life simple for yourself: Work as if the names are always case sensitive.... and don't be lazy, don't everything lower case. Names like AddressFirstLine, and SerialNumber are easy to read. The benefits far outweigh a little bit of digital (as in fingers!) inconvenience.
The second column lets you select the data type for the field. (I have a whole tutorial on data, or field, types for you. It is an important topic, but if you want to leave it until later, then use the type "Text[VARCHAR]" for most things, and "Integer [INTEGER]" for things that you may want to do arithmetic with, e.g. numbers of widgets in stock. (With "Integer", fractions are not possible. If you need fractions, use "Real[REAL]"... but avoid it if you can.)
You don't need to put anything in the third column, "Description". It's role is obvious. It can be a good place to provide guidance for your users on the way you want data coded. For example, if the field is for a "yes" or a "no", but you have decided that this must be entered as "y" or "n", the description is a place where you can put that information, and have it presented to your users at the right moment. If users hover over the heading of a column, the description comes up as a tooltip.
Once you have specified a field type, boxes appear at the bottom of the screen which let you set various things. For now, the default values will be okay, unless you are told to use something special.
You must have a primary key in every table. Often it is quite satisfactory simply to use an integer which is automatically filled in for you, by ooBase. If you use the wizard to create the table, it takes care of creating such a primary key. This field won't be "good for anything"... other than supplying your database with the obligatory primary key. It will give each record "a number".... not "human friendly", but some kind of primary key is needed by the computer.
If you are using the design view to create the table, and you want an integer primary key, it is still easy.
The primary key should be in the first field. You can arrange that as follows:
If you already have some field names entered, and you have not yet left the Table Design window for the first time, you can insert a new row by right-clicking, in the first row, on the gray box to the left of the "Field Name" column, and selecting "Insert Row". (If you have left the Table Design window before seeing this, "play with" what you have, you may be able to fix things... but if it doesn't go well, you may find it quicker just to start again!)
Name the field ID, or something similar. Make the field type "Integer". Use the property setting boxes at the bottom of the Table Design window to turn on the "Auto-value" option, i.e. set Auto-Value to "yes". The system-supplied "Auto-increment statement: Identity" is satisfactory.
Once you have set up the field thus, right click on box at the left hand end of the row, and click "Primary key". A little golden key should appear in the first column. That's it. You're done.
A detail: You might be tempted to try to use a different data type for the primary key. The only other type which offers Auto-value (which is a great convenience) is the BigInt type. The "Integer" type allows you 2,147,483,647 records per table without getting clever. (Twice that if you do get clever.) This very generous provision comes at the minor cost of a slightly extravagant use of memory / disc space. I doubt you'll ever have too many records for an Integer key, and I wouldn't lose any sleep over the slight waste of memory / disc space. If the size of the record is critical, and you are willing to sacrifice the Auto-value convenience (or provide it for yourself some other way), and you know you won't have more than 32,767 records then you can use the Small Integer data type... and save five "spaces" per record.
The values in the primary key for the different records in a table must all be different. There are times when you must set up more complicated primary keys. Will you need to soon? Probably not if you are just getting started with databases. There is more on primary keys in other tutorials. One bit of "primary key cleverness" follows, and then we will move on.
Pretend you are setting up a table to hold the names of some employees. Pretend that you are going to keep the surname in one field, and the first names in a second field. Unless you are a very small firm, the surname will not be suitable for a primary key. As soon as you have two "Browns" (or other), you're in trouble. (All of the records must have something different in their primary key fields.) If you can be confident that you will never have two employees with the same full name, you could proceed as follows. (Of course, as soon as you design your database, foolishly, like this, you will find that a second "Joe Smith" joins the firm... in which case you will have to cheat and rename him Joe2 Smith, or use some such dodge. Messy. And not the way to make an employee feel valued.)
But if you "dare", or are working on something similar where you won't get two "Joe Smith's", then all you need to do is....
Set up a first field called "Surname", and a second field called "FirstName".
Make your primary key the combination of the values of the first and second fields, as follows...
You can set up such a primary key using the table creation wizard. It does the job right. Alternatively, in ooBase version 3, at least, you can use the design view. It will allow you to select two fields (use shift-click) and designate them as the primary key. (A long time ago, with ooBase 2, and when I was less experienced, I had some trouble when I tried making multi-field primary keys. The "with the wizard" approach worked even then, and I would recommend you upgrade to version 3, if you haven't already, anyway.)
Just before we move on, some advice: While there are times when you will have good reasons for using multi-field primary keys, in general, I would try to avoid them. They seem to me to be a great way to slow down the operation of your database. Of course, machines are so overpowered today you probably wouldn't notice the database struggling... but why introduce complexities? And remember that the database that works well with 10 records may be wonderful, but when you've added the other 500 records, to make the database actually useful, as opposed to a little toy with some test data, you may find that it isn't as nimble as it was with 10 records. When designers care about such things (as they must) they ask "Is it scalable?" I.e., can it be scaled up, to cope with many records?
Once upon a time...
Once upon a time, I sometimes had a difficulties when using the table creation wizard. I found that if, during the wizard's execution, I availed myself of the chances it gave me to change the names or data types of fields, then I sometimes had trouble at the end of the wizard when I clicked "Finish". To get around this, I began using the wizard only to "rough out" a table, providing it with enough fields for my needs, but not being fussy about the names or types of the fields. When the wizard had finished creating the table, I then opened it in the design view, and changed the field names and types. After I'd done this a few times, I asked myself why I didn't just create the table using the design view in the first place. I haven't had these problems for a while. Either I'm not doing something I used to do, or there was a "feature" which was ironed out. Even in the "bad" old days, I could always get the job done.
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .