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) 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 ®, 6/07.
In this tutorial you will see how to set up a data entry form for two related tables.
In isolation, what we do may see a bit pointless, but...
If you want to get ahead in database design, it will be worth your while to take a deep breath, and force your way thought all that follows.
There is some overlap between the contents of this tutorial and two others I've published, but all of them are talking about important concepts, and I believe that it can be helpful to look at things from multiple directions, when trying to master them. (The other tutorials were Getting started with multiple tables, relationships and "Using multiple tables and relations")
The database used for illustrating what is taught in this tutorial happens to be for managing stock market investments. Fear not! The same tables and forms are used in many applications. Those other applications simply use fields with different names, and perhaps slightly different data-types.
Underlying the example are two tables. One lists the names of some stocks, and the other table lists economic sectors into which the stock market can be split up.
You may have come to this tutorial from a longer essay that I'm working on. If you have, you may already have a database called PDB058. If not, create it now, and create the following two tables. (See my "getting started" tutorial if you want help with "create the database and two tables".)
The two tables should be set up as follows:
Table: StockNames, consisting of 4 fields:
Tick, type Text(fix), length 10 Dis, type Text(fix), length 1 Sect, type Text(fix), length 3 Name, type Text(variable length), max length 30
... and...
Table: CodesSectors, consisting of two fields:
SecCD, type Text(fix), length 3 (table's key) SecDescrip, type Text (variable length), max length 10
The key for this table is made from the first two fields. (I'll explain what the fields are for in a moment.)
For the sake of the tutorial, enter the following data. Contrary to general good working practice, just access the tables directly to put this data in. (In general, you should create a form, and enter data through that.) I would recommend that you use the same case as I have used, e.g. dru, Drugs, PFE, not DRU, drugs, pfe, for reasons that boil down to "because I say so." You don't have to... but it may pay dividends.
CodesSectors:
dru Drugs tec Technology
StockNames:
PFE a dru Pfizer IBM a tec International Business Machines GOOG a tec Google
That's a good start!
Just before we move on, a word about the fields in StockNames. The last field (Name) is the everyday name for the company a given record is about. The first field ("Tick") is the tickertape abbreviation for that company, an abbreviation used in many contexts across the investing world, and carefully managed by the stock exchanges, so that you have a good chance of finding what you want with nothing more than the ticker code. There is a slight fly in the ointment. Take "S". That was the ticker for Sears & Roebuck. That company is gone. In June 07, the ticker "S" is being used by Sprint. The second field in StockNames is a "disambiguation" code. I'm calling Sears "Sa" (ticker "S" + disambiguation code "a"), and calling Sprint "Sb" in my records. Note that these codes are not universally agreed. Note also that some companies have multiple classes of stock, often called "class A" or "class B". For instance, I own some Hubble Class B. If my disambiguation code for that is "a", it will end up being called HUBBa in my records. Hubble trades on the NYSE, so it should have a three letter ticker... HUB. The second "B" is for the class. Depending on what system you are working in, you ask about Hubble Class B stock with HUBB, HUB.B or HUB-B. Pity "the big boys" can't seem to agree on a standard way of dealing with multiple classes of a given company's stock, but that's the reality. Deal with it?
The sector field is the interesting one. Before we talk about how that field is built into our database, perhaps I should say what a "sector" is? It's what you probably expect: Investors expect companies which do similar things to have similar characteristics. Two energy sector stocks, say BP and Exxon, are likely to have similarities. Mr. Bush takes us to war, and the share prices of most energy sector stocks go up. At the same time, the share price of companies in the airlines sector go down. The investor worries that fuel prices will rise, and the airlines will have trouble raising their prices enough to maintain their previous profits. It is interesting that the market assumes that the oil companies will do better if oil becomes harder to obtain. Of course the oil companies are selling oil, and the airlines are buying it. So: A company's "sector" indicates the general part of the economic ecology the company is part of.
In our database, we have a table listing the names of the sectors we choose to split everything up into, and abbreviations we've chosen for those sectors. We have a field in the StockNames table where we will use one of those abbreviations to characterize a given company. This approach will save us a lot of typing, and chances for typos.
What we have done may seem "obvious", but it is at the heart of a big part of what is wonderful about relational databases. What are they? The meaning of that adjective is something that you will gradually absorb as we go along. To get you started: There is a relationship between our two tables, isn't there? The "sector" information in the StockNames table records relates to information in the CodesSectors table's records.
Moving on: Close any ooBase things you may have open, apart from the main ooBase project management window (see next paragraph) and (optionally) the "help" window. Leave the database open.
(What's the "main ooBase project manager window"? It has 3 panes: One fills the left side, is headed "Database", and allows you to select Tables, Queries, Forms and Reports. The other two are one above each other on the right, one is Tasks and the other lists the available entities for whatever part of the database system you are working on.)
Next, we'll create two forms which can be used for looking at, or editing, the records in the tables.
Select "Forms" in the main ooBase project management window's left hand column. Then click on "Use Wizard to Create Form." Don't be alarmed by the OpenOffice Writer page that opens... your "Database Form" will be a fancy ooWriter "Document"! Note that the Wizard's steps are indicated in its left-hand column.
Step 1: Select Table: CodesSectors, and move both of the available fields to the "Fields In The Form" panel. (The easy way is to click on the "<<"). And Click Next.
Step 2: Don't set up a sub-form. Just click next to go on. You will skip over steps 3 and 4, and find yourself at...
Step 5: You want to arrange the data controls as a datasheet. The appropriate selection has probably already been made. (You'll see "as datasheet" where it is needed.) And Click Next.
Step 6: Again, what you want is probably already selected: "The form is to display all data". Don't disallow anything. And Click Next.
Step 7: Apply Styles: Play with this if you must. I just took the defaults. And Click Next.
Step 8: Give it the name CodesSectorsDataEntry; leave the setting on "Work with the form", and click "Finish". (If "next" is not greyed, you've probably made a mistake. In this case, click "Next", and you may be taken back to where the problem was first important. Note: That may be after the place where you did what needs changing.)
If all's well, you should now have a form which looks very like the table looked when you manipulated it directly.
Try adding another record:
SecCD:ene
Sec:Descrip:Energy
And, for reasons which should become clear, another (if not already present)
SecCD: mis
SecDescrip: Misc (for "Miscellaneous")
Adjust the size of the window that form is in. Leave it open.
Follow similar steps to create a form called StockNamesDataEntry which displays all of the fields of the StockNames table.
Hurrah! You're two steps down the road to becoming the next stock market tycoon!
We have a small database that will hold the names of companies, and a little bit of information about them.
Let's make a deliberate mistake.
Enter a record in the StockNames table with...
TEST / a / xxx / TestRecord.
What's this "xxx" sector? (Companies in the porn industry are not, as a rule, listed on the stock exchanges.) It is a test, to see what happens if we enter "bad" data. All of the entries in the sector field should be abbreviations present in the records that constitute the CodesSectors table. Checking that no unspecified sectors are in use is a large part of the reason for the CodesSectors table. Either the data entry operator has mistyped something, or the person who said that the company was in sector "xxx" is imagining that a sector exists for which there has no proper provision. It isn't hard to "properly provide" for a sector, even a new sector, but the system is built in a way that encourages discipline.
At the moment... and we're going to "fix" this... the system allows the "bad" sector "xxx".
(Delete this test record, to avoid problems which will otherwise arise in a moment. (To do this: Right click on the rectangle just to the left of "TEST" in the "Tick" column, and then click on the "Delete Rows" which you ought to see.))
Our database "works", on a superficial level. However, good programs for managing data allow you to set up rules which prevent the entry of something like "xxx"... a sector abbreviation not defined in the CodesSectors table. And we're going to set those rules up in a moment.
There's another thing that a good program for managing data will let you set up: Something to stop you, say, deleting the "dru / Drugs" record from the CodesSectors table as long as there are any records in the StockNames table which use that code. "Referential integrity" is the name for what is ensured by such rules.
Close anything that is open, apart from PDB058 itself, the main ooBase project management window and (optionally) the help window.
In developing this tutorial, I got to this point, and then tried to do what the Help file told me: Edit | Database | Advanced Settings... but "Advanced Settings" was greyed out. Tried shutting down, restarting. Same result. But! What I want to do can be done...
On the menu bar of the main ooBase project management window, click Tools | Relationships. The "Relation design" window should open.
Use the "Add tables" window to add, one after the other, StockNames and CodeSectors to the main "Relation Design" window.
Arrange the representations of the tables so that StockNames' is to the left of CodesSectors'.
Put your mouse pointer in the middle of "Sect" (in StockNames). Depress, and keep depressed, the left mouse button. Move the pointer to the middle of "SecCD" in CodesSectors. Release the mouse button. (It is as if you were trying to drag "Sect" onto "SecCD".)
Get an error message? You will if you forgot to delete the TestRecord record, sector "xxx", as you were told to above.
You should now see a line between the representations of the two tables. (Move the tables around to make the line sensible.)
Before we go further: A little information. If you see "CodesSectors.SecCD", that is just an efficient way of saying "the SecCD field in the CodesSectors table".
At the left hand end, you'll see a little "n", and at the right hand end a "1". The line says that there is a "one- to- many" relationship between CodesSectors.SecCD and StockNames.Sect.
"One- to- many?" you may be asking.....
It means that any given value for the field will be found in only one record in the CodesSectors table, but there may be many, any number ("n") of, records in the other table where that value appears.
Think about it. It would be silly to have a given sector code stand for more than one sector, but there are many companies in the same sector, so many of the company name records will be, for example, "ene".
If a field is a table's primary key, you cannot enter the same data in two records. That protects us from inadvertently creating a new sector which re-uses a code which we've already allocated to another sector. We cannot, say, make an entry in CodesSectors like "ele / electric utilities" if we already have "ele / electronics". The program for managing our data is giving us protection against something we might do by accident.
Previously, we could enter "bad data" in the Stocknames table. We could enter "TEST / a / xxx / TestRecord". (This would be bad because there is no sector with "xxx" as its code.)
Close the "Relation Design" window.
Re-open the StockNames table. Try entering "TEST / a / xxx / TestRecord". It will let you enter that... but as soon as you try to leave the record, ooBase will complain, as it should, alerting you to the fact that you are trying to enter bad data. ooBase puts it more formally: "Error inserting new record: Integrity constraint violation"... I.e., the integrity of your data would be gone if this record became part of the table. Change the "xxx" to a code that IS present in the CodesSectors table, and the new record will be accepted. Brilliant! This is Really Clever. Don't let your by- now- glazed eyes fail to notice how marvelous this is.
I hope you remember that we created a sector called Misc, code "mis". This was so that we can always enter a new record in the StockNames table, even when we aren't sure what sector to use to characterize the company. We just say, for the moment, that it is in the "mis" sector. From time to time, we would probably ask ooBase to list all companies in the "mis" sector, and we'd try to assign at least some of them to a more meaningful sector. (It is easy to ask ooBase to show just the "mis" companies.) If we wanted to put them in a not- yet- provided- for sector, we could... we just have to go to the CodesSectors table first, create the new sector and assign its abbreviation, and then we can change any company's sector code to the new sector.
A few odds and ends....
To quote the ooBase help file: "If the Relation Design window is open, the selected tables cannot be modified, even in table Design Mode. This ensures that tables are not changed while the relations are being created."
What I just quoted shouldn't come as a surprise, if you think about it. Don't give ooBase too much to cope with. If you are working on setting up relationships, it would probably be best if the tables were closed. And vice version. Do one thing at a time. It won't significantly crimp your style.
A detail: You can edit the appearance of the forms. You don't need to, so skip past this if it doesn't come easily.
Close anything that is open, apart from PDB058 itself, the main ooBase project management window and (optionally) the help window.
Click on "Forms" in the project manager's left hand pane; right click on "StockNamesDataEntry", select "Edit". Your form will open in ooWriter, because the form is "just" a (complex) ooWriter "document". Now we get to the tricky bit. Click somewhere along an edge of the table (Tick/Dis/Sect/Name) which is an object within the form. What's tricky is clicking in the right spot. If you get the right spot, you will select the table, which will be shown by the fact that it now has little green squares at its corners, and in the middle of each side. If you drag these, you can re-size and reposition the table on the form. You can also resize the whole window. Save what you've done and close the window. I know that your changes to the table's position and size will be saved. I think that the state of the window when you closed it will be saved. Now use the ooBase project manager to open the form normally, i.e. for mere data entry. (Do this by double-clicking on it's name.) Table a better size? I hope so! As I said... if not, don't worry about it.
I had my form open for data entry, not form editing, and managed to get things in a tangle. I had some rows that I didn't want, and right clicking didn't bring up the "Delete row?" option. I just closed and re-opened the table, and all was well. I'd been opening and closing all sorts of things, any one of which could have made the table "write only". (A controllable "write only" option is probably available. It would be useful, for instance, in a stock control program where you wanted junior employees to be able to enter data, but not remove it.)
I have every confidence that during normal operations you won't get into the tangle I was in. And it was easy to get out of, in any case.
By the way: The "read only" that you see in the window's title bar doesn't mean that you can't add or remove records to the database. It means that you can't (at this point) alter the form you are using to edit the data.
Remember why we hoped that ooBase incorporated referential integrity tools? What happens, say, if we try to delete the "dru / Drugs" record from the CodesSectors table while we have a record in the StocksNames table consisting of "PFE / a / dru / Pfizer"? Try it! Hurrah! ooBase says, in more complex terms, "No, won't.".
This may all seem complex, dry, unnecessary. Take my word for it: It is wonderful, and using the tools presented in this tutorial will pay dividends. For a little project like a list of telephone numbers, you are unlikely to have any need of these tools. But if you want to go further with database design, these tools will be part of what you need. With them, you create robust, reliable, efficient databases.
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 . . . . .