Bookmark this on Delicious
Recommend to StumbleUpon
You may find that the database included in OpenOffice (and Libre Office, I believe, but I make no procises for that variant) 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, 10/12-7/15.
I seem to revisit the matter of "relationships" (in the database context!) repeatedly.
Perhaps because they are so important?
I will direct you to more detailed essays in a moment, but first, an attempt to explain the basic concept concisely.
... leaving out some of the possibilities....
To talk about "relationships", you need at least two tables in your database. (Terms put in bold typeface the first time they are used are used throughout this page in the narrow, database, sense of the term.
Imagine a database to track what gold medals were won by whom during an Olympics....
For this you could use a schema with two tables in it. There would be one table with a single record for each competitor who has won even one gold. In that table, we'll call it "People", there would be fields such as name, age, height, weight.
You would NOT try to have a field for "golds won". Not even a field for "how many". And certainly not multiple fields... one for "first gold", "second gold", etc. That (poor) solution is fraught with problems. (For a start, how many would you provide for? And when someone wins "too many", what would you do? And it gets worse the more you look at it.) (And neither would you want to do something Access makes easy: have several medals in one field, i.e. "Brown won gold in 100m and 500m"... which is a Bad Thing, too.)
Rather, you would have a second table, which we'll call "WonGold".
The table "WonGold" would have fields for "Event" and "Winner". (Things like 50m men's sprint, 50m women's sprint, 100m men's sprint, etc, would each have their own record. I.e. WonGold will have one record for each of the golds to be awarded.
Please allow me to stipulate that in the Olympic Games we are discussing, there are two competitors named Joe Smith. Happily, one is 22 and the other is 25. Let's say that the Joe Smith aged 22 won the 50m sprint, and the Joe Smith aged 25 won the 100m sprint. If we filled in the "WonGold" table as follows....
Event Winner
50m Sprint Joe Smith
100m Sprint Joe Smith
... it wouldn't be clear that it was different Joe Smiths.
I admit that two Joe Smiths is a stretch, but, even if this example stretches credulity, the situation could arise, and similar things do arise, and with databases we "work smarter", and deal with the "might happen" things.
Every table has a primary key made up of one or more fields. (In our examples here, our primary keys will be of the simple type: a single field.)
For the "WonGold" table, the primary key could be the event name. Leaving out the case of two competitors tying for first place, only one gold is awarded per event, and so the contents of the "Event" field will be different for every record in the table, as is required of primary key fields. (Of course, we'll need to include something to designate the sport. "100m" won't do... is that the 100m Field Sports sprint, or the Swimming event. Hence: "100mFS", "100mSwBS", "100mSwFS". (SWimming BackStroke, FreeStyle". See what tricks database people get up to?))
Going back to the "People" table we're developing, I am going to implement an overly simple primary key system... but it will do for the needs of our example... The table "People", with some names filled in, might look like...
PrimaryKey Age Name
JoSm22 22 Joe Smith
JoSm25 25 Joe Smith
JaDo23 23 Jane Doe
It would be better, by the way, to work with year of birth, rather than age. One remains constant, the other keeps changing. If you went ahead with age, it would have to be "Age at time of Olympics". And what about someone who had a birthday on, say, the third day? Again... "petty"? No. These are the sort of details you need to attend to, to build a successful database.)
With the tables "People" and "WonGold" set up as above, we have a chance to use a relationship.
With a relationship you can "tell" the database: Do not accept a value in the "Winner" field of "WonGold" unless what is going in that field appears as a "primary key" in the "People" table.
This really is a Big Deal. You may not immediately see why... but the more you use relationships, the more you will see what a great thing relationships are.
Just before we leave our little example, let me mention four things...
The code for a given person, e.g. JoSm22, can appear in the "People" table only once. That is in the nature of what a primary key is.
But! There is nothing to stop JoSm22 (or any other person's ID) appearing multiple times in the "WonGold" table. This is just as well, of course, as the 22 year old Joe Smith might well win more than one medal.
Next thing: Suppose you make a mistake? Suppose you discover that the third person in our sample data, Jane, isn't 23. She's actually 24... and you discover this after you have made several entries in the database for medals she has won.
If you have set up a relationship between "People.PrimaryKey" and "WonGold.Winner", and if you have set it to execute "cascading updates", which is optional, then when change Jane's code to JaDo24, all of the entries about Jane in the "WonGold" table will immediately be revised, too. Fantastic! (Beware: If you have both tables open, do such an update, you need to... a) move out of the record you have updated, to save it into the database, and then b) "refresh" the second table before you will see the effect of the cascade.)
Third thing: Suppose you are tidying things up, and incorrectly believe that Joe Smith, aged 25 hasn't won any medals, and you try to remove him from the "People" table... even though there's an overlooked-by-you record in the "WonGold" table showing him (by his code, JoSm25) as the winner of some event? What happens if you try to delete Joe's record from "People"? What happens is that the database says, "No, you can't. We need that in order to tell what there is to know about this "JoSm25" who's listed in the "WonGold" table." This is Good! Annoying... but good.
Fourth thing... and this is a "biggie". Take a deep breath. Fetch another coffee. Sorry. But (again!) it is important....
Most of this page is about putting relationships into your databases. There's another "thing" you are going to use in your work, which takes a bit of time to see as a distinct "thing"... joins between fields. You use these while building queries. The concepts are similar. The Open Office Base tools for managing them are similar. You have to keep yourself clear as to whether you are dealing with a relationship or a join at the moment.
Happily, the properties of a relationship and of a link are quite different. If you have "a thing" and you are not sure what it is, carefully go into editing it. (I say "carefully", as you may want to "cancel out" after looking at it, so that you don't disturb its settings.)
A relationship will offer you various update and delete options. Joins can be "inner", "left", "right", "cross", and "natural"- or not.
Even better news: If you are editing a relationship, the window title is "Relation Design" and for a join it is "join properties". Easy... when you know to worry, and know where to look!
Sadly, I only (re?)"discovered" (at a conscious level) this vital distinction 1 December 2012... after more than 25 years of fighting with relational databases on and off. I don't say this to shatter your faith in your guide... rather to stress that databases are not easy, and if you are struggling, don't be downhearted. You don't have to stagger around struggling in the dark as I have had to. And even someone who is confused on important major concepts can get useful work done.
I am working thorough my essays, expunging the consequences of my previous lack of understanding. Until that process is complete, if you see the word "link", be suspicious. Relationships and joins are both "links". If I am using the word link, it isn't as clear as it should be, and you need to decide: "Is this a relationship or a join", and proceed accordingly.
Speaking of "working through..."/ "fixing things", I would like to thank the people who take the trouble to write to me to alert me to errors, or things which aren't clear. This page had some horrendously confusing errors which someone wrote to me about, sparing you trying to guess what I meant by things that said some confusing things! (And he even told me which page he was making suggestions about, and pointed me towards how I'd got myself confused, while writing!)
Sorry. Things get better from here!....
I hope you begin to think that telling your databases about relationships between fields of tables is a Good Thing?
I seem have to addressed the question of "relationships", setting them up, etc, several times over the years. (Partly because I was sometimes actually talking about joins.) I intend to do some work on the following, pare away some of the overlap. For now... Sorry!?... the three tutorials available to you are as follows. (The titles are perhaps not always as indicative as they ought to be of the page's contents.)
Referential Integrity: This discussion is in the form of a case study. It looks at using a database to track stock market investing, and declaring relationships comes up along the way. Not, at the moment, perhaps the best essay of the three... but quite focused.
Multi-table Forms and Queries: This "wins" over the next in being shorter... and is newer, and thus I knew more at the time I wrote it. I'd suggest you try it before you tackle the next one. In it you will create a database with two tables, and create queries and forms to extract information from the tables. This tutorial does not involve any report... the "result" is a form that assembles your data in one easy to use window. It is a quick, "normal" tutorial... but doesn't cover everything the next one does.
Using Relationships: This is a monster... but it does cover a lot of ground.... much more than just "how to do relationships"! It had heavy editing in June 2007, and is more "mature" than the other two, perhaps. When you have a basic grasp of ooBase, and are ready to move on from simple things, and have an hour to spare, I would commend it to you. This tutorial also covers generating a report based on a query. Although longer than the others, this is possibly better, may make less demands on you and your time. Try them all?!
And there's an essay on relationships and joins in a short(er) page I've done talking about these important concepts in general terms.
Enjoy! I hope at least one of them has what you have come here for! Remember that this is just a "sub menu" of pages with discussions of relationships. There's also the main menu to the Sheepdog Guides Open Office database tutorials, if you are looking for something else.
For the example developed at the top of the page you'd do the following. It is best to do it before putting data in the tables. If there is data which breaks the rule the relationship would establish, you'll get an error message when you try to establish it. (In that case, edit the data, try again.)
Creating the relationship... the line from "People.PrimaryKey" to "WonGold.Winner".....
Note the "1" and the "n" at either end of the relationship line.
They are saying that for any given value, there will only be one record with that value in the "People" table, but there may be any number of records with that value in the "WonGold" table.
Creating the line is easy... when you know how. You just...
The line, with it's "1" and "n" should just appear. It doesn't matter if you start at "WonGold.Winner" and move to "People.PrimaryKey". There's only one end the "1" should go on, and ooBase will get it right.
Remember the point? Once this is set up, you can't put a value in "WonGold.Winner" which does not appear in the "PrimaryKey" field of the "People" table. (And there are other benefits.)
I swore I'd keep this page short. Sigh.
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.org. Mostly passes. There were two "unknown attributes" in Google+ button code. Sigh.
. . . . . P a g e . . . E n d s . . . . .