AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS Open Office database tutorials.
Delicious.Com Bookmark this on Delicious     StumbleUpon.Com Recommend toStumbleUpon

Open Office Base (database) Tutorials
Table constraints by Foreign Keys.

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/18.

Data validation: Another attempt

I've tried to write about this subject a number of times over the years. Maybe, with the benefit of those earlier attempts, I can today (12/18) do a better job. I hope so. It would be nice to move on from this topic.

The topic is "How can the database designer help users avoid entering invalid data?"

Many times there are only a limited number of valid entries for a given field in a table you have created. For example, if you have collected data on a group of people, and you have a reason to want to know each person's gender (and you are going to be "old fashioned" and only recognize two... please... can we proceed as if that would be acceptable?) then you'll have a field which should only hold "male" or "female", or some other pair of codes, e.g. "m"/"f".

Continuing with our example: It would not give a human any trouble if sometimes you recorded an individual as "male" and other times you recorded him as "man". Computers do not thrive on inconsistency. It would be best if you used one and only one code for each gender throughout the database. Table constraints are one tool you can use to guard against invalid entries. I have shown how ListBoxes and ComboBoxes can help elsewhere. I have written about using table constraints. In this essay, we explore constraining data by using foreign keys... the technique I most often use with my own data.

For this tutorial we will pretend that we've been given the job of keeping track of the names of the children attending a certain prestigious boarding school where every child is a member of one of the following "houses", (i.e. internal units of the school): Ravenclaw, Gryffindor, Hufflepuff or Slytherin.

We'll build a simple table, allocate "codes" to stand for the four houses, and create a system, based on a second table, and a relationship, which will prevent us from entering an invalid value for the child's house.

I may have made inappropriate use of the term "Foreign key" here. I hope not. Whatever you call it, the following technique is useful!

The school list database

(I've used this database in at least one other tutorial, apart from the one on ListBox and ComboBox.)

Create the database.

Create a table in it. Call the table HSL. (For "Hogwart's School List")

In it, provide the following fields:

ID integer, auto value, primary key
Name, type Text(fix)[CHAR], length 30
House, type Text(fix)[CHAR], length 3

Create a form. You might as well use the wizard. (How appropriate!)

You only need a very basic form. Include all fields, use "datasheet" arrangement. Call it HSLwLookup for "Hogwart's School List With Lookup". Exit the wizard, electing "Work with the form" as what you will do next... even though you won't. After the wizard closes, close the form which will have just opened.

Don't put any data in the table yet. If you have entered some data, then make sure that no record in the "House" field holds anything other than "gry","huf" or "sly". (Be sure there are no "rav" records yet, so I can illustrate something later.)

Close the form, because we are going to work on the database some more.

Add another table

Call it Houses. The fields are...

HouseShort, type Text(fix)[CHAR], length 3, entry required, primary key
HouseLong, type Text(fix)[CHAR], length 15

Put the following three records into the table...

HouseShort HouseLong
  gry       Griffyndor
  sly       Slytherin
  huf       Hufflepuff

(The second field is not relevant to our objective of creating data constraints... but it is typical in this sort of table, and will (if I have the energy!) be discussed elsewhere.)

Here's the tricky bit!

You may not have yet encountered a powerful tool provided by OpenOffice Base. The Relationship manager.

I'm afraid it won't solve your problems with boyufriends/ girlfriends/ spouses.... but it will be a friend in your database work if you master it.

((q-alt text for image))

Use "Tools/ Relationships" from the menu of the Open Office Base main project management window.

That should give rise to what you see to the right. Use the "Add" button, and the HSL table will be added to the relationship design we are starting. Use the mouse to select the other table, the "Houses" table, click "Add" again, and close the "Add tables" dialog, and with a little click/dragging, you will have what you see on the left, below.

((q-alt text for image))

Make sure that you put gry, huf, sly into the Houses table, as HouseShort.

Make sure that no record in your HSL table has anything other than gry, huf, or sly in the House field.

We're ready for the Big Deal. We are going to "establish a relationship" between two fields, one from each table.

Put the mouse pointer on "House" (in the diagram of the HSL table). Don't worry about the "can't do that" icon which pops up in a moment.

Drag from there to the "HouseShort" entry in the Houses table.

And release the mouse button.

You SHOULD get a line between the two fields, saying that a relationship has been created between them.

There should be a small "1" at the Houses end of the line, an "n" at the other end.

This is to say that a given value only appears 1 time in the Houses table, but can appear multiple times in the HSL table.

Save the relationship. The diagram should look like this...

((q-alt text for image))

Go play with the tables. Add a few pupils to Griffyndor.

Try to change a pupil's house to "rav", for Ravensclaw... but remember we haven't yet put that in the Houses table. You should fail.

Put rav/ Ravensclaw in the Houses table, and move your cursor out of that line of the table. Until you've done that, the new record has not been "posted" to the table.

NOW try entering a "rav" pupil. Should work okay.

And there you have it!

There you have a nice system for validating data, limiting the amount of bad data that gets into your database.

You can still, of course, put a pupil in the wrong house. But you can't, say, put a pupil in "syl", through a typo.

Sorry.. abrupt end...

It's late. I ache. I am hungry. I hope the above is useful?

Editorial Philosophy

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. Deta8ils 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.)

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.

Valid HTML 4.01 Transitional 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.

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .