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 ®, 3/06.
As with any case study, I hope the techniques presented here will be of general usefulness. But, Gentle Reader, imagine if you will that you are in charge of SourceForge.net, the great internet site which helps the open source community bring us new applications.
The good news? I have put a copy of the database online, which you can download. N.B.: I don't think you can open it with ooBase version 2.xx. It may not be exactly as described below, and "building" the thing from scratch may aid your understanding... but if you'd rather download an approximation of what follows, you can! You will find more than the basic tables in the database. The other things are described in other parts of this case study.
Sourceforge provides sites to projects. People sign up to work on projects. Someone, somewhere, probably wants to see who is doing what on how many projects, and get answers to similar questions. The database developed in this case study is for that hypothetical somebody.
The database will allow users to see....
The database will have three tables:
Don't worry about that third table just yet... it will make sense in a moment. The first two tables are pretty straightforward.
The records in the People table hold whatever you need to know... apart from what projects they are part of... about the people involved. For this case study, we will greatly abbreviate what you would typically record. However, beyond "the obvious", there is a "secret ingredient", which is of vital importance: The "PersonID" field. This will be an abbreviation of the person's name. No two people will be assigned the same "PersonID", and thus, besides the other things we want it for, it is ideal as the table's primary key. For the purposes of this case study, we will simply use the first two letters of the person's surname and first name, so Charlie Brown's PersonID will be BrCh. While this would not work in the real world.... Chantelle Broughton would have the same PersonID, it is a good start in a robust and user (almost) friendly scheme. (You can be sure that things will end in tears if when you design your database, you assume (makes an "ass" out of "u" and "me") that you won't get two people names resulting in the same PersonID under such a simple scheme.)
The next paragraph may give you a sense of "deja vu"... don't be disoriented... it is different (and similar) to the previous paragraph... with reason. When you can build symmetry into a database, benefits arise.
The records in the Projects table will record whatever you need to know... apart from what people working on them.... about the projects. For this case study, we will greatly abbreviate what you would typically record. However, beyond "the obvious", there is a "secret ingredient", which is of vital importance: The "ProjID" field. No two projects will be assigned the same "ProjID", and thus, besides the other things we want it for, it is ideal as the table's primary key. We will assign ProjIDs as follows: First will be the date the project was initiated, in yyyymmdd format. And appended to that will be a letter, so we will be able to start up to 26 projects per day. (Yes, if we used both upper and lower case letters we could start 52 projects per day... and endless confuse ourselves. Not least for the ease of reading and typing them, we will confine ourselves to lower case letters for the last character of the ProjID.
Now we come to the last table, PeopInProj. This table will seem badly designed to some readers. But, I promise you, it is the way to go. And it illustrates something you will see over and over in database work.
Before I say more about the PeopInProj table, let's generate some hypothetical data for the other tables. Set them up as follows, with the sample data given. Put the whole thing in a database called FDB7PeopProj to help us with things to come. I was using OpenOffice 3.0.0 on a windows XP machine while I wrote this, by the way.
The People table will have these fields:
The Projects table will have these fields:
(The project name might seem to be something that could replace the ProjID, but for reasons which will emerge, you want a short ProjID which won't change. A project might want or need to change it's name as it matures.)
If I was feeling "clever", I might make the description field be of type "memo"... but that would distract from the core purposes of this case study.
Fill those tables with the following sample data....
PeoplePersonID Surname Firstname Eddress BeHe Bell Henry cb@somewhere.net BrCh Brown Charlie snoopy@heaven.com JoAr Jones Arthur aj2@123.com SmJa Smith James jp@chouse.co.ukProjects
ProjID ProjName Description Category 20090125a LunhamLabels Prints address labels Office 20090126a FarWatch Remote monitoring Security 20090130a TSP10 Word Game Education 20090201a MGA14 Learn images Education
(The information above, by the way, was moved from ooBase to ooCalc simply by selecting "copy" after right clicking on the table's name in the main ooBase project management window, and then clicking "paste" in the body of an empty spreadsheet. I then selected all the text, did copy again, and pasted into my text editor (TextPad), and, after adding a few tabs, I was done!)
Now we have what we need in order to revisit the PeopInProj table, learn more about it, fathom its use and purpose.
Set up a PeopInProj table with these fields:
In the PeopInProj table, in the Project field, we will enter a project ID found in the ProjID field of the Project table, hence the data type and length must be the same for PeopInProj.Project and Project.ProjID. (Note those shorthand ways of saying "The Project field of the PeopInProj table" and "The PProjID field of the Project table".)
Likewise, in the PeopInProj table, in the Person field, table we will enter a person ID found in the PersonID field of the People table, hence the data type and length must be the same for PeopInProj.Person and People.PersonID.
Each record in the PeopInProg table will document one person's involvement with one project.
Some people may be involved in only one project; others will be involved in many.
Some projects will have only one person working on them; others will have attracted many people.
The glory of the data structure, or "schema" described above is that there is virtually no limit to either the projects a person is involved in, nor to the people involved in a give project. And also we haven't set up a lot of wasted space, which would happen if we'd organized things more along the lines that we might have, if we didn't know this "trick".
(The Role, Joined and Left fields are not essential to the basic objectives of this case study, but they open up the possibility of using this database to illustrate further things, another time. Role is to hold a code saying what the person's role is in the project, e.g. team leader, archivist, chief tester, associate tester, etc, etc. The two dates are for recording when someone joined a project and when they left it. No entry in the "left" field implies they are still participating in the project.)
A detail: On one hand, making the "Entry Required" property "yes" for Person and Project makes sense. On the other, it makes adding batches of data annoying, as you can't go down one column at a time.
Some sample data for the PeopInProj table is listed below, but N.B.: If you are working with a downloaded copy of the database, you will not be able to enter Project or Person IDs which are not values already present in the "Project" or "People" tables. (This is a Good Thing, and how it came to be part of the downloadable database is explained near the start of part two of this case study.)
PPID Project Person Role Joined Left 0 20090125a JoAr head 01/01/09 1 20090125a BrCh assist 01/01/09 2 20090130a BrCh head 02/02/09 3 20090125a SmJa assist 01/01/09 30/01/09 4 20090130a SmJa head 20/01/09 5 20090130a BeHe assist 20/01/09 6 20090130a JoAr assist 25/01/09
That's a very limited set of data, but it already illustrates a number of things,
The last three lines tell you that project "20091030a" has three people working on it: James Smith, who is the head of the project, And Henry Brown and Arthur Jones.
We have our tables. If you haven't designed your tables well, you will never "save" a database project. However, until you start on the queries, forms and reports to use the tables, you don't... until you have some database design experience... know if you have a good design for the tables. It is a nasty Catch-22. Try to learn the rules of table design, and stick to them.
This part of the case study ends here, with the tables set up. In part two we will explore using them. The downloadable database is the same for all parts of the case study. It has the tables described above, and the other elements described in part two.
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 . . . . .