AUTHOR'S MAIN SITE
> > > > >
TABLE OF CONTENTS for version 2 Open Office database tutorials.
Open Office 2 Base (database) Tutorials
Using the Design View to create a database's first tables
You may find that the database being shipped with OpenOffice (ver.2) delights you as much as it has me. This page, and the others linked to it, can help you use it.
Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.
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.
Page contents © TK Boyd, Sheepdog Software ®, 3/06.
A nice, simple, short note... hardly a tutorial at all! ________
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. I may go back and re-write some of my tutorials, telling people normally to create tables via the Design View alternative. The suggestion of using 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 all 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. You are likely to be using it to access the HSQL engine which comes with Open Office (version 2). If so, you are working in a SQL environment.
When working with SQL, sometimes when you speak of "a database", you are 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 even contain a table. (It is pretty useless like this... just the container, 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 ooBase project manager, 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." You can do this by either of the two ways you can always create a table: with "Create Table in Design View" (i.e. using the Design View tool) or with "Use Wizard to Create Table."
Elsewhere, I often suggest that you invoke the "... using table wizard" option. The more I use ooBase, the more I doubt that this is your best course. When you start a new database, you can elect simply to "Open database for editing" at the end of creating the database (the "container"). If you do this, you are taken to the project manager page, 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.
Using the design view to create a table
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. Just be sure you don't confuse the Table Design window for a datasheet showing a table (rows of records / columns of fields).
One row per field. The columns are pretty self explanatory.
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 "DOB", you can't subsequently refer to it as "DoB" or "dob", etc.) 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, and make 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 'til 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.)
The third column, "Description", I have not used yet. It's role is obvious. I don't know exactly when and where what you enter reappears. It may well 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 may be a place where you can put that information, and have it presented to your users at the right moment.
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.
Some fine points, some pros and cons ________
In most tables, you should have a primary key. Often it is quite satisfactory simply to use an integer which is filled in for you by ooBase. If you use the wizard to set up the table, it takes care of everything for you.
If you are using the design view to create the table, and you want an integer primary key, it is still easy.
Make the first field the primary key, by means of the following steps:
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 grey box to the left of the "Field Name" column, and selecting "Insert Row".
Name it 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". That's it. You're done. (If you right-click on the box again, you'll see a tick mark in front of "Primary Key". Click it again, and the field reverts to not being the table's key.)
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 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 two "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. But just so anyone who needs it will have the information....
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 the primary 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.)
Make the first field Surname. Make the second field FirstName.
Make your primary key the combination of the values of the first and second fields.
To set up such a primary key, use the table creation wizard. It does the job right. The design view will allow you to select two fields and designate them as the primary key. When I tried making a table with this sort of primary key by the two methods available, I found that the table created using Design View didn't reject multiple records with identical primary keys... as it should have. I probably just missed a tick-box somewhere.
Just before we move on, some advice: While there are times when you will have good reasons for using multi-filed 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. The question that designers struggle with is "Is it scalable?" I.e., can it be scaled up, to cope with many records?
One bit of slightly bad news to end with...
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.
Editorial Philosophy
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.
Ad from page's editor: Yes.. I do enjoy
compiling these things for you... I hope they are helpful. However..
this doesn't pay my bills!!! If you find this stuff useful, (and you
run an MS-DOS or Windows PC) please visit my freeware and shareware
page, download something, and circulate it for me? Links on your page
to this page would also be appreciated!
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
One last bit of advice: Be sure you know all you need to about spyware.
. . . . . P a g e . . . E n d s . . . . .