Right forum for this?

Discuss the database features
Post Reply
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Right forum for this?

Post by DynV »

I'd like some help on how to create a DB I'm interested in; it doesn't have to be OO Base, but will likely be (even if I get the DB creation help from somewhere else). I've used OO Base a few times in the past and I think I'll manage adequately without help on that aspect. What I have no idea is how to make the table, queries & such for the result I'm looking for. I see this with different layers such as
  1. Real project (ie: warehouse procedures)
  2. DB conceptualization
  3. DBMS DB integration (of the former point)
  4. DBMS interface creation
and from this overview I'm having an issue with #2.

Can this forum help me with this? If so, perhaps I'd receive better free help somewhere else? If another service would be preferable, do you have a suggestion (ie: links) ?

Thank you kindly for your help
Je suis francophone.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Right forum for this?

Post by Villeroy »

IMHO, the best place would be a class room. A forum is definitively a bad place to learn programming. This forum has some endless (and fruitless) topics where someone tries to solve something without having the necessary background.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Right forum for this?

Post by John_Ha »

See why you should never use Base for a business application database and why you should buy a business solution.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Right forum for this?

Post by RoryOF »

A book often recommended for information on design and use of databases is
Base Tutorial:
From Newbie to Advocate in a one, two... three!


This will give you much useful information, but the best advice in the long run and cheapest solution is to use a commercial application.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Right forum for this?

Post by DynV »

Villeroy wrote:IMHO, the best place would be a class room. A forum is definitively a bad place to learn programming. This forum has some endless (and fruitless) topics where someone tries to solve something without having the necessary background.
I did have some but they weren't advanced, it also was some time ago; so I do have--some--background.
John_Ha wrote:See why you should never use Base for a business application database and why you should buy a business solution.
It's for a personal solution; if for some reason others use it, it would only be an afterthought and its main goal would be my usage.
Je suis francophone.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Right forum for this?

Post by UnklDonald418 »

Two books on the subject
"Data Modeling: A Beginners Guide" by Andy Oppel
"Database Design for Mere Mortals" by Michael J Hernandez
Check your local library, they often have these and other titles.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Right forum for this?

Post by DynV »

I did go to class and did read books with the caveats in
DynV wrote:
Villeroy wrote:IMHO, the best place would be a class room. A forum is definitively a bad place to learn programming. This forum has some endless (and fruitless) topics where someone tries to solve something without having the necessary background.
I did have some but they weren't advanced, it also was some time ago; so I do have--some--background.
I'm not asking if there's a place to make the whole thing for me, I'm asking for a place where I could ask those kind of questions, if it'S not a good place here, I'll ask some other place; and if it's the case, do you have a suggestion?
Je suis francophone.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Right forum for this?

Post by Villeroy »

I think, all questions related to database design are slightly off topic, not really related to this office suite. We only deal with them when the underlying database is an embedded HSQL since this is the one and only type of database that can be created with OpenOffice (plus embedded Firebird with LibreOffice).

Once you have your MySQL/MariaDB/PostgreSQL/MS SQL/Oracle/H2/HSQL/whatever database up and running, you may ask questions about the queries, forms and reports of a Base document, database fields, linked ranges and pivot tables in Writer and Calc. You hardly get qualified answers to questions that are related to other database engines since there is only a small group of volunteers able to answer these questions and because you never design these databases with the help of your office suite.

The "development tools" provided by Base, the wizards and designers for HSQL tables, queries and forms are poorly designed plastic tools. Only the report wizard and the report builder are worth being used. Non-trivial Base forms are made by hand starting with the forms navigator and a blank document. Most of my tables are made by SQL statement because it is easier and quicker to do so.

If you are determined to build a database for your own business application, I would think that Base is not suitable. It offers embedded HSQL 1.8 and LibreOffice offers embedded Firebird. HSQL 1.8 is certainly not the right database engine for a business application because it lacks a lot of features and is no longer developed. LibreOffice's Firebird integration does not work yet. Your final product has to be extracted from the Base document anyways since the embedded version of a databsae puts your precious data at risk while lacking network connectivity, user managment and multi-user access.

You should start with one of the heavier database irons, do real development work with real developer tools on a real database server with multi-user access, performance testing and useful features. For the development work you would consult a forum or mailing list that deals with your chosen database engine. If you got this far, you may also use a better tool set for input forms on the desktop and for online input forms via html/php.
And finally you may connect the database with your office suite in order to do office work with database data. This includes everything related to the Base connector, Writers database fields, Base reports, linked pivot tables and linked import ranges in Calc, possibly some input forms for specific office related tasks.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Right forum for this?

Post by DynV »

Something that need to be readressed, formatting from this post,
DynV wrote:
John_Ha wrote:See why you should never use Base for a business application database and why you should buy a business solution.
It's for a personal solution; if for some reason others use it, it would only be an afterthought and its main goal would be my usage.
and the chance of me publishing it is like 1/4, and even if I did it's for a PC game that's almost 20 years old for a particular issue, so not many potential users.
Je suis francophone.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Right forum for this?

Post by UnklDonald418 »

Actually, you can ask anything you like.
General questions, as you have seen, will usually prompt advice or very general answers.
Despite the dire warnings here, there are businesses that happily use Base databases. They are usually quite small businesses with specialized needs that don't require the capabilities of more robust databases.
Look at the top of the Base forum page for the link to Base Tutorials.
At the top of of the Base Tutorials page is a link to Examples where you can download working databases. You might find one of those can serve as a starting point.
Make frequent backups, because the Embedded database that comes with OO and LO is susceptible to data loss. An odb file is actually a zip archive and WHEN something goes wrong with the zip process the odb file can become corrupt and beyond repair.
There is a fix, but that cripples the table design GUI, so structural changes to tables must bypass Base and work directly with the HSQL database engine using SQL commands
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
DynV
Posts: 196
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Right forum for this?

Post by DynV »

UnklDonald418 wrote:[...] An odb file is actually a zip archive and WHEN something goes wrong with the zip process the odb file can become corrupt and beyond repair.
There is a fix, but that cripples the table design GUI, so structural changes to tables must bypass Base and work directly with the HSQL database engine using SQL commands
Do you mean I must bypass Base tables changes for the fix to be possible? Or that if the fix is use it (Base tables changes) won't be possible anymore?
Je suis francophone.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Right forum for this?

Post by Villeroy »

Download http://www.mediafire.com/file/qpiuiq0sc ... e.zip/file
Extract it to a "trusted directory" according to Tools>Options...Security>[Macro Security]>Trusted Sources
Open the database document. The embedded macro will connect the document to the database folder using the hsquldb.jar in the driver folder.

It is just a small demo for the built-in DATEADD function shipped with HSQL2 which calculates dates, times and timestamps from a date and a number.
The status bar indicates that the connected HSQL database resides outside the document.
The table designer has limited access to the underlying table structures. LibreOffice 6 allows me to add columns and edit relations but I can't edit existing columns. With menu:Tools>SQL... I have full control over the database because it allows me to talk directly to the underlying database engine. I could also use more advanced development tools since the stand-alone database is editable with any software that can handle JDBC databases.

This external database is by far more secure than the embedded one.
This external database can be run in server mode providing multi-user access. I run such a HSQL server since 9 years with zero problems.
This external database can be used online and offline with any tool set able to query data from a JDBC database.

The Open/LibreOffice database user won't notice any difference. Forms, reports, office documents work as expected with this database, just like they would with a MySQL database or something else.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Right forum for this?

Post by UnklDonald418 »

Often someone not well versed in SQL will do the initial development in an Embedded database and then copy the tables, queries, forms and reports into a JDBC (also referred to as a Split) database.
If you are comfortable with the SQL commands for creating and maintaining tables then start with a JDBC database.

As a bonus you get an upgraded version of the HSQL database engine.
There are even more recent versions that can easily be installed by replacing the hsqldb.jar file in the driver subdirectory.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Right forum for this?

Post by Villeroy »

Modifiying existing columns or deleting them is the only thing LibreOffice does not allow me do with a split HSQL2. I can create and delete tables. The relations designer works. Using the ALTER COLUMN statement to modify existing columns is not difficult. You need to know these statements anyway because there are some situations where the table designer fails to modify existing objects although the modification is possible to do in plain SQL.
For the transition from embedded HSQL to split HSQL my my Python macro establishes a working database connection where all queries, forms and reports work as before. Only in some cases there are solvable problems. In most cases it works flawlessly.
When you omit the HSQL2 driver the macro creates a split HSQL1 connected by the office driver. The split HSQL1 works exactly like the embedded one plus user managment and reduced risk of data loss.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply