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 this web page we aren't going to solve any ooBase mysteries. This web page will guide you though setting up the tables underlying a small database which is ripe with ooBase tutorial possibilities. They are explored in other tutorials. In this web page, apart from setting the scene for future tutorials, we discuss sundry points in respect of setting up the underlying tables and relationships. We discuss data coding.
This is in the "case studies" section of my website about Open Office's database ooBase. Most of my case studies are more complete... they come with forms, tables, reports in addition to tables. But this one has more possibilities!
You can, by the way, download this database instead of creating it by hand. It is supplied in .odb form (ooBase, version 3 database) (not compressed), with the name FDB7isltraf, which derives from "FreeDataBase, 7 (case studies), ISLand TRAFfic".
Imagine a very small police force... let's say it is in charge of a (friendly) island in the Caribbean.
It wants to keep track of motor vehicle incidents.
Because this is a nice little Island in Paradise, a number of very convenient things are true, which makes our database work easy...
Names: If you take the first two letters of anyone's first name, and the first two letters of their surname, and put those four letters together, no two people will generate the same short-form name. Thus we can have a People table with a four character PersID field as its primary key, and Jane Doe's PersID can be jado.
Cars: There are under 1000 on the island, so the license plate can simply consist of a short number. (In British English, you could say: "Number plates ARE number plates") Our Cars table has a CarID field as primary key, and it just holds things like "1" (The governor general's car), "123", "88", etc. The bad news is that some people own more than one car. (We will assume that every car has only one owner.) More on this in a moment.
Incidents: We're pretty laid back in paradise... all we want is a list of what incidents have taken place, what car(s) were involved in each, and a brief description. Nothing fancy.
The sort of thing that the Chief of Police's IT person is going to be able to provide is, say, a simple form so that when a person is involved in an incident, the Chief can quickly, easily, get a list of all the incidents involving that person. A start on how the IT person could implement something like that is explained in the first tutorial based on this database. Others should follow.
Yes... the schema (design of tables and their forms) that follows is more complex than we need for our immediate wants... at least those stated so far. But it will serve us well as the basis of other database tutorials.
You've probably already worked out three quarters of the following, but just to be explicit....
The foundation of all we are going to do will be four tables. They are....
The People table will have the following fields. "PK" marks the primary key. "text 4" means that the field is of type "text", length 4. The third entry is a brief description of what we will put in the field
The Cars table will have the following fields.
The Incidents table will have the following fields.
The incident ID will be composed as follows: For the first incident of January 13, 2009, the code is 20090113a. For the next incident on that day: 20090113b. And so on. It is a nice quiet little island, and there won't be more than 26 incidents in a day, not even on the day of the annual big party. And the staff all know each other, so we won't get two "20090113d"s in a moment of confusion. The computer won't allow two, anyway.
For the description of incident, we don't need to be clever. It will be things like "speeding", "hit tree", "illegally parked", "cars collided".
Had you anticipated the "Involved" table?? That's the quarter I bet you had not anticipated. Let me tell you what goes in the Involved table... and explain to you that you shouldn't- in a database- try to do a very human thing that many, many people try to do when they are learning about setting up databases.
People would like to be able to have a field in the Incidents table called "Involved". And in that, if they had their way, they would store the IDs (plural, in some cases) of the cars involved in any given incident. You "can't" do that with sensible databases. Each field can hold one thing. Okay then, novices say, we'll have, say, 3 fields for "Car1", "Car2", and "Car3", and in these we will put the IDs of the cars involved. Bad idea. There will be some single vehicle incidents... and the empty Car2 and Car3 fields will come back to haunt you. There will be incidents involving more than three cars, and then you are really in trouble. And in any case, which car goes in which field? No, that's not the answer. The answer is a separate table I'm going to call "Involved".
The "Involved" table will have the following fields.
Now... at first this will seem to "waste" a lot of space, be a cumbersome "answer"... but believe me: It is The Way To Go. In a similar database problem, that of managing orders for, say, an online hardware store, it may surprise you to learn that an invoice for an order for three items will involve three records in an "ItemsOrdered" table... each will consist of the invoice number and a code for one of the items in the order, e.g.....
INVOICE ITEM 0100 hammer 0100 saw 0100 screwdriver 0101 ... (next order)... etc
Seems weird maybe, but that's the way it is done.
The "InvolvedID" field holds a "meaningless" number. I prefer to use keys which actually "mean" something, intrinsically... but I couldn't find a sensible mechanism here. (I thought of one or two not- sensible mechanisms, by the way. Be glad I controlled my enthusiasm.)
The Fine field is for how much the driver was fined... it can be 0, if the driver wasn't responsible for the incident. In our nice agreeable island, the currency doesn't trouble us with "pennies", all amounts are whole number amounts.
Some sample data....Each row in the examples is one record
People table:
Cars table. (Note that Jeremy and Tom each have two cars):
Incidents table:
Involved table: Quite similar to the Incidents table... but note that there are two records for the "cars collided" incident.
Note poor Jane Doe's Bad Day.... fines both for speeding and for illegal parking. Note Jeremy C seems to speed a little more than others. And he even, you will see, if you look at the data closely, got a speeding ticket while driving Tom's car! (All characters fictitious, of course; any resemblance to persons living or dead, etc, etc. Or even to people who have stolen the identities of persons living or dead. (Free licence to use one of my shareware programs to the first person to explain that little joke. I will alter this note to declare the prize won, should that occur.))
... so good. Now we need to put the various relationships in place. Speaking of which, be careful to keep clear in your mind WHICH, say, PersID field we're talking about. Both the People table and the Involved tables have PersID fields, for instance. Happily, we can just write People.PersID and Involved.PersID to make clear which we're talking about.
Relationships....Set the following. (Doing that is explained in my introduction to relationships tutorial.) They are all "1 to many" relationships, and I've put the "1" on the left.
Apart from having other benefits, those restraints prevent us, for instance, from creating a record in the Car table with the owner coded with a PerID which doesn't exist.
Whew!! At last. NOW we can "play" with those tables. You can, by the way, download this database instead of creating it by hand. It is supplied in .odb form (ooBase, version 3 database) (not compressed), with the name FDB7isltraf, which derives from "FreeDataBase, 7 (case studies), ISLand TRAFfic".
That's all you get... here. Other tutorials (see list at top) work with the tables we have set up.
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 . . . . .