The following describes a hypothetical database. Many, many similar tasks could be taken care of with nearly identical databases. If you aren’t interested in books in libraries, you could still find the following interesting or useful!
It is presented here for two reasons…
- It may interest database novices as an exercise in what is called “schema design”… the choosing of tables and fields for a task
- It is a careful statement of what I have so far, to supplement questions of the “how do I…?” sort at the Libre Office (Base) user forum.
If you want to “play with” the database presented here, you can either build your own copy from the description that follows, or you can simply fetch the .odb from http://wywtk.com/libo/fdb/odb/fdb041.odb Your browser may flag that as a “potential security risk”, and it is. ODB files can contain macros. I trust you have anti-malware software in place? (I like eSet.)) Which do you hate more: taking risks or typing? (^_^)
(I’m using Win 11, with LO: 7.1.1.2 (x64) / LibreOffice Community. This description first created 1 Oct 22.)
—
I’ve set up a demo of a database I am using to keep track of the books in a library.
Embedded Firefox as database engine
I wanted to use the embedded Firebird engine and hadn’t recently tweaked my LO, so first, “once and for all”, had to do the following. I was rusty on the details, but, hurrah for Ratslinger… I know THAT name of old… there was already a guide at the forum…
https://ask.libreoffice.org/t/firebird-option-missing-for-new-database/51869
Q: I wanted to start a new database using Firebird but that option was not available.
Ratslinger answered, April 2020. (Tweaked by me):
Save and close any open LO documents. Leave it running, if it was, start it, don't change what's in any document that opens, if it wasn't.
On the dialogue that Tools->Options brings up, under LibreOffice, Advanced, you must place checkmark in the box in front of "Enable experimental features."
You may get a “Restart now?” from LO. don’t worry, it is only talking about restarting your LO.
Then close all open LO instances.
(“Do it once” config task help ends here.)
Embedded Firebird as the engine should now be available to the “New database” wizard.
Once that was out of the way, I used the wizard to create a new database, with the following tables…
** Main table: “Book”… note: “Book”, not “Books”. Why clutter the system with “s”s? We know there will be more than one record in the table? (Details of what goes inn each in a moment.)
Primary Key: BookID- Text[VARCHAR], length 12
Other fields…
Auth- Text[VARCHAR], length 30
Title- Text[VARCHAR], length 30
Cost- Integer, length 10 (the default length for the data type)
** Secondary table: “BookWhere”
Primary key… a compound key, consisting of…
BookID- Text[VARCHAR], length 12
When- Text[VARCHAR], length 6
Another field…
“BookWhere” has one further field: Shelf- Text[VARCHAR], length 10
– – – – – – – – – – –
In Book, the BookID field will hold something you might call a “serial number”… A short string of characters assigned to that volume. (If I have several copies of, say, Winnie-The-Pooh, each would have a DIFFERENT “Book ID”. If I have a “book” that comes in several volumes, they would have BookID values similar to the values used with other books, plus a digit…
f22a01a1 and f22a01a2 would be two volumes of the same “book”. (Another book, added to the library that day (Oct 1st, 2022) might have f22a01b for its BookID.)
Auth is where the book’s author is recorded.
Title is where the book’s title is recorded.
Cost is where the book’s cost (rounded to the nearest dollar) is recorded.
In BookWhere, WHERE the book should be… which shelf it should be on, is recorded in the “Shelf” field. Each shelf in the library has a unique short string of characters to identify it. There is a method in the madness of assigning those strings, but they need not trouble us here.
I hope the use of “BookWhere.BookID” is self-evident.
“BookWhere.When” is an indication of WHEN that book was seen on that shelf. I use a simple code to denote a date, and allow myself to add a letter at the end of the date code to allow several BookWhere records for a book that moved what shelf it was on more than once in a day. Thus the table not only records where each book is //now//, but it also shows what shelves the book had been on in the past. This may seem unnecessary, and something which will lead to the table’s size bloating, but it seemed a good idea at the time.
===
A “relationship” (in the narrow, relational database sense of the word) was established between the two tables…
Tools/ Relationships was used to say that Book.BookID and BookWhere.BookID are in a relationship. In Book, a given BookID can appear only once. There’s only one record in Book with a given value in BookID. (The fact that that field is the table’s simple primary key demands (and enforces) that.) However, In BookWhere, a given BookID can appear multiple times… as long as each record with that ID has a different value in the BookWhere.When. (Making BookWhere’s primary key a compound primary key made that possible.)
By the way… a simple thing… but just to touch on it, in case it isn’t obvious to you: A record cannot be entered in BookWhere that uses a value in BookID unless there is already a record in Book with that value in Book.BookID. (This arises out of the “relationship” that was created.)
In the diagram that arises when the relationship has been set up, there is a line between Book.BookID and BookWhere.BookID. There is a “1” beside Book.BookID and “n” beside BookWhere.BookID, the “n” standing for “1 or more”.
At the moment, novice that I am, I have “no action” selected in the properties of the relationship. This may come back to haunt me, but I loathe “stuff” I don’t properly understand lurking in the background, waiting to “do things” without my knowledge!
===
The tables were given some data… The first image shows the data in the “Book” table…
The second shows the data in the “BookWhere” table…
I hope the entries in the Book table need no explanation? Notice that it shows two copies of Winnie-the-Pooh in the library.
The BookWhere table shows the following. Before you look at that, be advised that the table happens to list the records in the order of the shelvings. It wouldn’t necessarily do so, but of course, by the “magic” of using a database, anything on screens or in database-generated hardcopy could be presented that way.
So… table shows…
-Book “f22621b” (title: Seven Pillars) was put on shelf “7nc22” on “22915” (15 September, 2022)
-Book “f20c25a” (the expensive copy of Winnie) was put on shelf “7nc22” on “22915”
-Book “f22901a” (the inexpensive Winnie) was put on shelf “2rr10” on “22915”
So far so good?
Then we come to the fourth record.
It says that on “22917” (September 17th, 2022) book “f20c25a” was “put” on shelf “7nc15”.
The table shows that previously it was on shelf “7nc22”.
Fine! Why not! It had to be SOMEWHERE previously. The design of the tables is not distrubed by the fact that when we “put” that book on that shelf, we were moving it from a different shelf. We will want to move books from time to time!!!
====
And that’s the story of this little database for tracking books in the library.
Now that “the database” is set up, I’ve found that I have some questions about how I would USE the database, and how I would set up queries and reports to get what I want OUT of the computer. But the underlying structure to accept the data about the library is (more o less!) “done”!
====
I have posted all of this as a question on the LibreOffice forum. If you are very kind, please help me with suggestions there? (I don’t enable comments on my blog posts. Too many silly people around, sigh.)
=================
Help sought: What query would return a table with the following columns…?
Book.ID
Book.Author
Book.Title
BookWhere.Shelf
BookWhere.When
If I get that far, I think, hope, that it isn’t much more to ask to get the table sorted…
1st- on BookID
2nd- on When
===
“For extra credit”…. What would be the way to obtain a list similar to the above, but with just the latest record for any particular book… I.e. just one record for any given BookID.
Help MUCH appreciated!!!
Odds and ends 