Is LibreOffice Base the right tool for my situation?

Hi! I freely admit to being a newb, and will be as succinct as I can and try and edit this down/organize as much as possible. Feel free to ask for more detail. However, FYI, I’m really terrible at conciseness and knowing which details are important/which aren’t. If anyone has tips on this, especially quantitative/specific ones, I’d love to hear them.

Now, down to business. :slight_smile:

Objective:

  • Trying to create a database to keep
    track of mods and custom content that I’ve downloaded for
    a game
  • Hoping to be able to share
    the project–not the data itself, since
    everyone who plays the game will have
    a different set of downloaded mods/CC, but the
    structure of the database and such,
    so that anyone who wants to (hopefully including
    non-coders) can have their own
    database of their content (to make it
    easier for them to know what they
    have, what might need updating/be
    broken as of the latest official game
    patch, etc.)
  • Basically, I’d love to have this
    function a bit like
    Calibre,
    except for mods/CC instead of ebooks,
    and without all the other fancy
    features Calibre has like ebook
    conversion, etc. (And also Calibre is, as far as I understand, its own entirely separate program, where mine would preferably be more like…like sharing a template for a database, kind of? Like, I’m envisioning sharing a basic “template”-type file, with things like forms/queries mostly built in but blank or sample data, and then also sharing a download link to LO or whatever program(s) necessary.)

Examples of data I might include:

  • Name/title/whatever of mod or CC

  • Creator(s)

  • Description

  • (Preferably) thumbnail/photo of item or what it does, if applicable

  • Date you last updated it

  • Download URL

  • Creator URL(s)

  • File location

  • Compressed file location (if applicable)

  • Game pack(s) necessary for use (if applicable)

  • Maybe custom tags, if possible and not too terribly difficult to implement

  • etc.

Criteria for choosing a database tool:

Absolutely necessary:

  • No money spent (I just don’t have any
    RN, and I don’t think other players
    would generally want to spend on
    something like this either)
  • Pretty darn sure that what I want is
    a relational database, since I’ll
    probably need/want to include
    many-to-many relationships that will
    need to be resolved and stuff

Necessary if at all possible:

  • Would like to be able to use both GUI and
    SQL methods to create the database
    (this is what led me to LO in the
    first place–was looking for
    Microsoft Access alternatives because
    I have Access but wouldn’t be able to
    easily share my finished product because the software costs)
  • Would
    like to be able to mostly do the
    creation and putting-together of the
    database myself (I’ve seen a couple
    of tool options that seem to be more
    for completely non-coders, and I’d
    rather not use that kind of thing)
  • Basically, I’m just trying to keep
    this as simple as possible while
    still learning/practicing something
    in the way of database
    design/SQL/computer science skills.
    Many of my projects tend to start out
    as one thing, then snowball and morph
    into ginormous half-impossible
    timesinks that half the time never
    get finished because I’ve burned
    myself out working on them…and I
    just don’t have the bandwidth for
    that now. So I’m doing my darnedest
    to keep that from happening

Strongly preferred:

  • As usable as possible for both those
    who know what they’re doing with
    databases/SQL and those who don’t (if I have to make 2 different versions, I might, depending on how difficult that is, but I would like to keep this as simple as possible)

Preferred but not too big a deal:

  • As little downloading things as
    possible (both for computer space and
    because I feel like other users
    (well, and me too!) won’t like the
    inconvenience/risk otherwise)

Specific stuff I can think of that I’m willing to compromise on, at least somewhat:

  • Online/cloud-based vs. software
    download is nbd. Like, online might
    be nice, but I could probably go
    either way

  • May be willing to do a bit more than I expected in the way of coding
    (as opposed to GUI), but preferably
    not too much (and also I don’t know
    VB, which I think is maybe what you use for
    macros (?), so that might be difficult). Like, I’m good with learning a bit, but don’t have the time/energy to learn an entire new programming language or anything like that at the moment

…I think that’s most of it. If I’ve missed anything I’ll try and edit later.

What I’ve already done:

…tbh some of it’s a bit of a blur, both because I’ve been doing so much with it (and I’m busy IRL), and because I don’t understand a lot of the terminology I’ve been seeing in conjunction with this. But a few of the things I do remember doing:

  • Internet searches (with various
    search engines) of different
    combinations of keywords like
    “Microsoft Access alternative,”
    “free,” “online” (I was originally
    hoping for something that was more
    cloud-based so no one had to download
    software), etc.

  • Looked at some of the entries/reviews on
    AlternativeTo.net

  • Looked at a few lists of reviews (the ones that are titled things like “5 Free Alternatives to Microsoft Access” and stuff)

  • Asked my dad, who’s a programmer at Microsoft–he doesn’t really do much with databases, but he knows a bit about it at least, and had a few suggestions that I looked into

Did a bit of research (some more or less in-depth, depending on what I found) on at least the following (I may be forgetting some):

Problems I’m running into: A lot of it has been that I have no idea what half the terminology used with this stuff means, and maybe just the general concepts–like, I think I understand the difference between, say, Microsoft Access and MySQL, but I don’t know the proper terms for those differences. I’m attempting to get a better grasp on it but not succeeding very well, I think.

Results so far: I’ve been watching a fairly comprehensive tutorial video series for LO Base (although IIRC it was posted like 6 years ago, so I’m taking it with a grain of salt), and the program been mostly looking like it’ll fulfill my criteria pretty well. But I’m still not completely certain, and especially as regards whether to use LO Base by itself or in combination with something else. I’d assumed I’d just use it by itself, but I came across a question on this site, and one of the comments reads as follows:

I might add that using the default
embedded hsqldb as the database engine
for such a db is probably not the
greatest of ideas, depending on
whether or not you are going to
include sequence data in one of the
fields. You would be better off using
a “split” hsql database (see other
posts on splitting hsql out for how to
do that) or else a server backend db
engine such as MariaDB/MySQL or
Postgresql.

Tbh, I don’t know what exactly the commenter means by “such a database,” because I don’t really understand the person’s original question or their DB structure–partially, I think, because I don’t know if some of the terminology they’re using (e.g. “feature”) is data-related (I looked up what a plasmid is, but the answer kind of made my head hurt; I’m not a scientist) or database terminology.

What I hope to gain: Basically, if someone could point me towards…well, anything, really, but especially any or all of the below…that would be awesome.

  • I’d love opinions on whether LO is
    the right tool for use here at all,
    and if so, whether I should use it in
    conjunction with something else or
    not
  • If you think LO Base isn’t the right
    tool (or even if you’re not sure), if you’ve got any ideas about
    other tools that do or might fit my criteria, that’d be
    awesome
  • An explanation in plain English of
    some of the terminology for this stuff would also be
    helpful, since at least that would
    hopefully help me make some headway
    on understanding the info about the
    different options I have

Again, feel free to ask for any details/info you need!

Oh, and my OS is Windows 10, though, as you might’ve gathered earlier, I’m hoping to find something that will work on most common systems. As for LO version, it’s 6.3.6.2 (x64). And currently I’m looking at using the default Base format (HSQLDB Embedded), but like I said, I’m not sure if that’s the best option or not.

What exactly is a Mod/CC, specifically the CC part?

There is nothing wrong with your questions. Overall it is a lot to take in, considering the number of questions you have, but all are valid questions.

By the sound of your problem I get the feeling that you are a man with a purpose and a vision but lack a clue.

If you need to support more than one OS then you increase the number of support issues that will arise. If you use an office suite to implement your solution then each user must become competent at handling the office suite. If there are a lot of mods to enter, scanning them in is far less tedious. If scanning is to be done then code must be written to perform the scan.

You will need to understand the problem before you decide on a solution. From the sound of it you need some programming and database skills. Your users will depend on you for support or be left hung out to dry when something stops working. Overall this type of project is a time investment.

From the content of the OQ it seems a mod is a modification and CC is custom content

Hey, sorry I’ve not been around much. My mental health has taken a dive the past few days, and I haven’t been up to much.

Yes, sorry about the jargon; forgot it even was jargon! @robleyd is right–modifications and custom content. (If it helps you understand, we’re dealing with Sims 4. Changes to the game can range from clothing/objects created entirely by players, to things that are just recolored/retextured, to changes in code to make Sims do/not do a specific thing, to massive projects that overhaul huge amounts of game functioning. Any of these (some more than others, esp. any with code/XML) have a chance of breaking themselves, other game parts, or both next time an official patch is released, & some people have thousands of CC items/mods, so it’s a lot of work to maintain your “library” but still have everything in your game work.)

Apparently there’s a character limit on comments, and also I need to go to bed, so I’ll try to reply, maybe in the IRC, at some point later.

@AmydelABC, anyone taking on this sort of project is bound to run into mental issues, at some stage…
Thank you for explaining what CC stands for. I still stand by my previous statement that each developer will need to become proficient at using Base, if they are to use it as a tool to solve this problem of tracking mods and CC. If you believe Base offers a shortcut to developing such a software then be warned, it is not a shortcut, it is just another tool that can get the job done. The question to ask yourself is “is it the right tool for the job?”. I would argue that it is not. I would also strongly suggest you get a dialog going on IRC as this post is getting very lengthy.

I shall try to summon up your questions, fill in the gaps and be as concise and clear as possible.

You are about to open up a new can of worms so be prepared…

Short answer: No, LO Base is not the right tool to solve your problem. Having said that, it might be of some use to you for analyzing and or presenting your data, think queries and spread sheets combined. Used in such a way you would be tying it into your actual solution which then is most likely based off of an external remote database solution.

You should aim for a Web based application or solution, should you need to share data among each other.
I would argue that office suites are intended for use inside of a tightly controlled environment. By control I mean that each user is on the same OS, locale and version of the office suite. Also the applications of an office suite are intended to be used or operated by people who are competent in how to interact with each application. Managing a document in any office suite is like handling a sharp tool, do something wrong and an accident can occur. For the user an accident typically means data loss, or loss of content. A LO Base application can be streamlined and locked down for end users to interact with in a safe way but doing so is not very intuitively achievable simply because this is not how an office suite is intended to be used. This is what separates an office suite from a free standing application designed to solve a specific task and that task only. In contrast to office documents in general, a specialized application typically locks the end user out from modifying the function in a way which would render the application unusable or cause data loss. Office applications allow modification to the document by default.

SQL stands for Structured Query Language. It is supposed to be the universal language that all databases make use of for interaction. Each database solutions typically has it’s own dialect of this language but they are all roughly alike but not 100% compatible. Truth be told, transitioning from one dialect to another when switching systems is not fun.

Before we get to Technical I shall first point out some practical issues which you will be facing.
In order to share data among your co-workers you will need a centralized database. This requires a remote database management solution such as FirebirdDB, MariaDB, MySQL, Postgres, or similar.
LO Base, like most software, can run into version compatibility issues. If you and your co-workers are on different versions or releases of LO Base, use different locale settings and or use different operating systems, you will sooner or later run into issues. You could create a data entry GUI that works one day only to find that for some users it is no longer functioning after they updated. This is not an uncommon problem, even for those running MS Access.
If the aim is to allow each individual to maintain their own set of data records but still be able to share this data, a centralized remote database will be needed. Even if you are all located in the same physical building on the same LAN and have the ability to share the LO Base document via a NAS. An embedded database can only be open on one host PC, at a time.

LO Base, is to my knowledge the only viable, available, cost free, stable enough alternative to MS Access.
HSQLDB used to be the default embedded database backend in LO Base. Today a shift towards Firebird DB is being done. Firebird DB is coded in C++ as opposed to Java and is hence more resource friendly and efficient. It also comes with other advantages.
Splitting of a HSQLDB refers to the desire to protect the database itself from corruption. Embedded databases have been known to get corrupted, rendering the data in them unreadable, any database can get corrupted it is just that embeded ones tend to be more prone to this problem. This can be caused by power outages, disk corruption, memory errors and so on. Some LO Base users for this reason take the database content of the LO Base document out of the containing zip file and link to it externally. An alternative is to use a remote database system: RDBMS. If say you wanted to access your database in a Web application then you would want a Firebird database since it can run on an independant remote server and be connected to from LO Base, Web apps and even SpreadSheets such as LO Calc (read only).

What exactly is LO Base and how does it compare to all those alternate systems you mention in your question? Well LO Base is not a system per say, it is a graphical tool used for building database oriented applications. Also it provides tools to create a graphical user interface or GUI, for the users of the database. Also it is part of a suite of applications that can be combined together to build a system that provides a digital alternative to paper. The alternative software you mention, such as MySQL Workbench are not such a tool. An example of how LibreOffice/MS Office would be used in a typical business environment, be it one man or a large office: Data is stored and kept up to date in a database. The data can be sorted, compared, filtered, updated. Other applications can make use of this data, such as in spread sheets for analysis, calculations and illustrated using charts and graphs.

MySQL on it’s own is a remote database management system. You would run it on it’s own then link to it from another application, such as from LO Base, MS Access or a Web application coded in perhaps PHP.
MySQL Workbench is an administrative and development tool for database developers and administrators. It provides a GUI for performing tasks that would otherwise normally be performed from a text console.

I hope this helps clear things up. If not then ask follow up questions.

List of useful reading, in no specific order:
Migration From HSQLDB to Firebird - Wikipedia
Firebird documentation
LibreOffice documentation - read the Base Handbook
Databases, history and general design - Wikipedia
SQL Tutorial - w3schools
HyperSQL with OpenOffice - hsqldb.org, a bit of history, migrating from embedded to external db and some other goodies.
Base: Firebird vs HSQLDB embedded database? - Why HSQLDB was replaced by Firebird DB.

Wow! I give a standing ovation! Excellent text, in simple words and about the most important. Add at the end a few links to popular literature (for example, Casanova) and this text can be placed unchanged in the Wiki, and referenced in subsequent replies. Please accept my thanks, @lazarus477

I am glad it hit home. I shall try to add some references. For anyone with such questions in their mind a few referrals to good docs is very helpful. The problem with the documentation is that no one piece of documentation will tie the pieces together in the way that I have in this text, there is no good overview. My text is based on years of studying each technologies and software’s docs and after years of hands on experience. This is why I can present this larger picture. What is really needed is some new documentation that explains these things as a whole. I am considering putting up such a work, we shall see.

Wow! Thanks for that reply; definitely helps clear some things up (and brings up at least one problem I hadn’t thought of, haha). Am I supposed to reply to what you said/ask follow-up questions in a comment on your answer, or is there some other way I’m meant to do that?

I see updates made to this page and I also have chosen to follow your posts. So if you have questions which go off in a different direction then I will see them, head there and if possible offer insight. As an alternative you might prefer live chat on IRC, irc.freenode.net, the channel is #libreoffice. I go by the same user name there.

@lazarus477,

Just a note. HSQLDB as a server (backend) is well supported. Was one of the first servers I installed when first starting with LibreOffice Base a few years back. See → HyperSQL Database Engine (HSQLDB)

@Ratslinger. I stand corrected. As he points out, HyperSQL apparently does support being run as a stand alone remote database.

Deployment options

  • Embedded (into Java applications) and
    Client-Server operating modes Three
    client server protocols: HSQL, HTTP
    and HSQL-BER - can run as an HTTP web
    server - all with SSL option Can be
    used in applets, read-only media
    (CD), inside jars, webstart and
    embedded applications Multiple
    databases per JVM ODBC support

Reference: Features at hsqldb.org

I removed my previous statement from the answer so as not to slander HSQL.