Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.
Bookmark this on Delicious
Recommend toStumbleUpon
You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that Open Office, including ooBase, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!
There's more about ooBase in the main index to this material. Adabas? Star Office? Ancient history.
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 fully explained, and there are more tips, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software, 12/12.
This page is not of my usual style. There is little "now type this" material here. You will find several other pages elsewhere on the site with that type of presentation in connection with relationships and joins.
As soon as you go beyond the earliest "crawling" stage in using Open Office Base, or any other relational database... there's a hint in the name... you will begin using more than one table in your database. (Beginners sometimes think that a table is "a database". While some very, very simple databases only have one table, they are very limited. Even if they can be useful.
As soon as you "advance" (begin to totter like a toddler) to having multiple tables in your database, relationships become enormously important. You aren't actually required to have them, but it would be foolish to have a multi-table database without setting up relationships. And the more you use them, the more you will see how useful they can be. Many things which you will want to do will require the presence of relationships. You might as well start using them, even if, at first, in a limited way, as soon as you start making multi-table databases.
(We'll get to "joins" later.)
The computer can't read your mind.
Let's say you have a database for signing books out of a public lending library. A very simple database!
It could rely on each book having a unique ID printed in the book. It, the book's title, etc, etc, would of course also be in the computer... but for our purposes it doesn't need to be. Note, though, that the ISBN alone won't do. Each book in the library must have a unique ID. That could START with the publisher-assigned ISBN... but then the library would have to add something like -1, -2,-3... to distinguish multiple copies of that book, if the library had them.
So... still no tables!
There would a table with the details of the people with permission to borrow from the library... their names, addresses, phone numbers, etc. AND a unique- to- each- person "ID code". We'll call the table "Customers", and the customer ID "CustID".
The only other table we need is a "BooksOutOnLoan" table. In a SIMPLE lending database, we would need only two fields: Book and Borrower.
BooksOutOnLoan.Book would be the table's primary key... a given book can only (in our alternative universe) be on loan to one person at a time, and we aren't going to try to track lending history. The other field will have a person's ID code entered in it.
If four books were on loan, the BooksOutOnLoan table might... if you had designed your ID coding very badly.. look like...
Book Borrower
BK003 Pers528
BK106 Pers528
BK007 Pers312
BK823 Pers492
I hope you see that the above "says" that one person (Pers528) borrowed two books, and two other people each borrowed one book. If you don't see that, struggle with it until you do, because it is important to things to come.
Now... you could run the database just like that.
But it would be so much better if you define a relationship which says that any entry in BooksOutOnLoan.Borrower has to be a string of characters which occurs somewhere in the Customers.CustID field.
Note I said "if you define a relationship". It is up to you. The computer can't read your mind. You have to tell it that the field "Borrower" in the table "BooksOnLoan" will should only ever hold values found in the "Customers" table, in the "CustID" field. Logical enough. A human would "just know".
The great thing is: Once you have put the relationship into the database, it won't LET you put bad data into the BooksOnLoan table!
A given database may have many relationships. The above was just a simple example.
The details are covered in various tutorials, but just to get the impatient started: You access the tool for telling the computer about relationships you want it to know about by going to the ooBase main project manager window. From its menu, you click "Tools"/ "Relationships".
Moving on... but still talking about relationships.
When you have a relationships set up in a database, you can set it to execute cascading updates, and/ or cascading deletions.
Suppose in your database, you had a complaint, and some customer didn't like the Customer ID you'd given then. Maybe you gave them "Pers911", and they have particularly unhappy memories of that day, and want a different customer ID. But they already have some books out on loan. (This is a slightly stretched example! But I hope you will be able to see how cascading could be useful in other circumstances!!)
Suppose "Pers876" hasn't been issued yet, and the customer would be happy with that. You change the entry in the "Customers" table. Do you then need to go into the "BooksOnLoan" table, and change any instances of Cust911 to Cust876? No, you don't... not if you have set the relationship up to execute cascading updates. Pretty cool, really. And it helps you keep the data in your database "good".
Joins are a little like relationships. Too much like relationships, for me, because I often get confused. Which I shouldn't. They really are clearly different. But I do.
Joins become important when you are building a query or a report which draws on material from more than one table. For the library, you might want to be able to produce....
Book Borrowed by
BK003 Doe, John
BK106 Doe, John
BK007 Smith, Francesca
BK823 Brown, Rebecca
Very nearly what we had before... but we have substituted the customer's names for their IDs. (We have the names in the "Customers" table, too.)
"All" it takes is a join between BooksOutOnLoan.Borrower and Customers.CustID. That join will be very similar to the relationship we set up between them earlier. If you create the report by making a query first, and then doing the report on the query results, then whether you create the query in design mode or with the wizard, Base will "slip the join in" without telling you what it is doing. The join will show in the query design screen, but unless you look closely, it looks like a relationship. But it is not the same thing!
I am out of both time and energy. I am going to stop there. You've had the important "heads up": Start getting these TWO things, relationships and joins, separate in your mind. There is much about relationships, and a growing body of stuff about joins in my various tutorials.
A plea: If you come across the word "link", where I might have meant "relationship", or I might have meant "join", please send me the page's URL ("www-thingie")? That will have been from the Bad Old Days before I knew there were two entities, and began to distinguish between them. The ambiguity and confusion need clearing up!
I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.
I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.
Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....
Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)
Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)
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.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.
. . . . . P a g e . . . E n d s . . . . .