Database or Spreadsheet ?

Talk about anything at all....
Post Reply
StanH
Posts: 2
Joined: Fri May 01, 2020 9:08 pm

Database or Spreadsheet ?

Post by StanH »

Having used Excel Spreadsheet in my professional career (retired 2011) in what circumstances is it more appropriate and productive to use a database instead ? The phrase "horses for courses" comes to mind.
OpenOffice 4.1.7 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Database or Spreadsheet ?

Post by Zizi64 »

Having used Excel Spreadsheet in my professional career (retired 2011) in what circumstances is it more appropriate and productive to use a database instead ?
This depends on the details of the task.

If you want make some calculations in a spreadsheet, the "equivalent" application of MS Excel is the AOO/LO Calc.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Database or Spreadsheet ?

Post by jrkrideau »

StanH wrote:In what circumstances is it more appropriate and productive to use a database instead ? The phrase "horses for courses" comes to mind.
I tend to be a bit cynical about spreadsheets and feel that they should not be used for anything more complicated than the weekly shopping list.

In a slightly more forgiving mood, a database is something that you would want to use when you have a lot of data. If you have thousands of rows of data it is much easier to use a data base' at least for storage. It is less likely that you will accidentally scramble the data, calculate something on a wrong column, etc.

You can fairly easily set up data integrity checks, particularly for input and databases have strong data typing.

Depending on how you do data processing, it can be much easier to verify/audit what you have done. Auditing a spreadsheet is hellish.

Overall databases are more difficult for a beginner to set up but once set up are easier and safer to use.

Here is a good example of why you might not want to use a spreadsheet for serious work The Reinhart-Rogoff error – or how not to Excel at economics
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database or Spreadsheet ?

Post by Villeroy »

StanH wrote:Having used Excel Spreadsheet in my professional career (retired 2011) in what circumstances is it more appropriate and productive to use a database instead ? The phrase "horses for courses" comes to mind.
Virtually nobody uses Excel for calculation models. Virtually everybody uses Excel as cheap database surrogate. One of the most popular misconceptions in IT history.
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
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Database or Spreadsheet ?

Post by jrkrideau »

Villeroy wrote: Virtually nobody uses Excel for calculation models.
True but the realing frightening thing is that people like financial "Quants" often do. That is when you suddenly notice a stock market crash or realize your local municipality has just gone broke and so on.

Used either way they are damned dangerous.
LibreOffice 7.3.7. 2; Ubuntu 22.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Database or Spreadsheet ?

Post by John_Ha »

jrkrideau wrote:Used either way they are damned dangerous.
Almost all spreadsheets used in business have errors in them - see 88% of spreadsheets have errors
StanH wrote:Having used Excel Spreadsheet in my professional career (retired 2011) in what circumstances is it more appropriate and productive to use a database instead ? The phrase "horses for courses" comes to mind.
For simple stuff a spreadsheet is fine. For complicated stuff and especially when you want add new function or change the design a database is much better as the concept is different: pour the data in to the database and keep it safe. Now write separate stuff to analyse the data. There is no risk of processing altering the data unless you decide to alter the data. But databases have a steeper learning curve. AOO/LO Base is quite poor - use a proper one.
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.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Database or Spreadsheet ?

Post by UnklDonald418 »

John_Ha wrote:
AOO/LO Base is quite poor - use a proper one.

Not sure what a proper one would be.
Base is a front end for a database engine and can be used with a number of different back end engines. The default Embedded Base database is a good learning platform, but it is dated, and isn't recomended for anything beyond learning. However, if you use a JDBC connection (also referred to as a Split database) an up-to-date HSQL engine can easily be used. You'll have to spend a considerable sum to get something that will outperform that configuration.
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
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Database or Spreadsheet ?

Post by jrkrideau »

John_Ha wrote:
jrkrideau wrote:Used either way they are damned dangerous.
Almost all spreadsheets used in business have errors in them - see 88% of spreadsheets have errors
I rest my case :D

I seldom use a database because of the type of data I work with but I avoid spreadsheets like the plague.

Here is another spreadsheet horror story, Gene name errors are widespread in the scientific literature
LibreOffice 7.3.7. 2; Ubuntu 22.04
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Database or Spreadsheet ?

Post by John_Ha »

See Spreadsheet mistakes - news stories for loads of horror stories - from Harvard reports, a $2billion fraud and countless errors of tens or hundreds of millions.
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
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database or Spreadsheet ?

Post by Villeroy »

StanH, are you still watching this thread?
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
HaroldLMNJ
Posts: 3
Joined: Mon Jun 22, 2020 5:36 pm

Re: Database or Spreadsheet ?

Post by HaroldLMNJ »

I have "databases" which I've created in Excel. They are fine for smaller lists and for simple calculations of one cell of data calculated from other cells within the same "table" or within the same workbook. Once these tables become large [in excess of let's say 50 rows, and more than a screens width of columns, it may be time to consider using a data base application.

I've used MS Excel due to my not using a database application in more than 20 years, and becoming rusty at all of the details in construction and more importantly the formatting of a useful database tool. I've come to this point now, where my Excel spread sheet which I depend upon to track sales results [management is equally as inept at using their accounting application for this purpose and not motivated to learn of give access to the portion of the accounting application so that I may use it].

With all if this said: it is my feeling that a spreadsheet is fine for simple small collections of data [grocery lists, wedding invitations et al]. For larger collections of data, nothing is a easy at data entry, and efficient as a well designed and well formatted database.
Apache OpenOffice 4.1.7, running on Windows 10
StanH
Posts: 2
Joined: Fri May 01, 2020 9:08 pm

Re: Database or Spreadsheet ?

Post by StanH »

Hi Harold.

I retired in 2011. Prior to that I was working with one of the major five UK construction companies. Our Quantity Surveyors (and other professions) used MS Excel on multi-million projects.

A project may involve dozens, if not hundreds, of sheets ( eg Plasterwork, Concrete Works, Steel Frame, etc. ) containing numerous rows. The sheets were linked to each other by formulae and to Section Summary Sheets and to say an Overall Summary Sheet and this demonstrates that Excel can be used successfully on very large collections of data. How that would be achieved in a database I can't imagine at this point in time because I'm still a novice.

Having said that I was working on a very large project in Sheffield (£45million in 1991) and we were using Oracle to manage our drawing management database which comprised hundreds of drawings and thousands of revisions on the old ms dos computers (pre-windows).
OpenOffice 4.1.7 on Windows 10
HaroldLMNJ
Posts: 3
Joined: Mon Jun 22, 2020 5:36 pm

Re: Database or Spreadsheet ?

Post by HaroldLMNJ »

jrkrideau wrote:
Villeroy wrote: Virtually nobody uses Excel for calculation models.
True but the realing frightening thing is that people like financial "Quants" often do. That is when you suddenly notice a stock market crash or realize your local municipality has just gone broke and so on.

Used either way they are damned dangerous.

Your reply suggests, correct that, blatantly states that using spreadsheets in financial analysis causes "...market crash ...local municipality has just gone broke...". If that was the case, politicians and financial executives would campaign and pledge not to use IT tools such as spreadsheets and use other tools when formulating financial policy for governments and for corporations.

Financial issues and policies in economics are not largely or directly attributable to a software tools. Every IT tool has it's value! The good thing about spreadsheets, any user can master their use, if they so desire, and if they are willing to invest the time in learning and understanding the cell computational formula syntax.

I am not an IT person! But I am certain that the formulas embedded within any cell computation are based upon well proven and well accepted mathematical formulas. Which have been around prior to the days of Lotus 123 and Plan Perfect! Remember those days? These are the same formulas used in database applications. It's not the IT tool, it's all in the user and more importantly it's the user who chooses to use or not use, or even pay attention to the facts the software is giving them.
Apache OpenOffice 4.1.7, running on Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Database or Spreadsheet ?

Post by John_Ha »

HaroldLMNJ wrote:I am not an IT person!
Exactly ... and why you should not be commenting on something about which you know so little.
HaroldLMNJ wrote: ... I am certain that the formulas embedded within any cell computation are based upon well proven and well accepted mathematical formulas.
It's not the formulae which are the biggest problem though they are often incorrect.

It is the fact that data in a spreadsheet is not protected from user error so it is easy to accidentally overtype or change something without realising it. Also, amateurs, as opposed to IT professionals, haven't the foggiest clue about keeping only one copy of a piece of data and always referencing that piece of data. If the same piece of data is stored in two different places it is merely a case of how long it will be before they differ. Amateurs don't protect the data in a spreadsheet.

Remember: when using a data base all the user data is safely stored and cannot be changed by user error. Any change to the data will be through a correctly designed and tested process which is locked and cannot be accidentally changed. Any calculation, however complex, which can be run in a spreadsheet can be run in a database application.
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
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database or Spreadsheet ?

Post by Villeroy »

This motorcycle is based upon well proven and well accepted mathematical formulas.
Image
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
HaroldLMNJ
Posts: 3
Joined: Mon Jun 22, 2020 5:36 pm

Re: Database or Spreadsheet ?

Post by HaroldLMNJ »

Your last post smacks with a great deal of cynicism, regarding users of spreadsheets and non IT professionals in general!

You probably make your living as an IT professional. Due to the pervasive spread of hardware and software in the last 40 years, you have probably seen demand for IT professional services decline!

I'm in manufacturing, have been my whole career! It's unreasonable to be cynical about companies which choose to purchase manufacturing services from companies in China, because of COST. Similarly, many IT tasks, both hardware and software can now be successfully performed by end users. The motivation is? COST!

As for backing up data; their are those that neglect this end of day task.But that is not a reason to cast a disparaging wide net on using a spread sheet versus a database. A database which is, "...correctly designed and tested process which is locked and cannot be accidentally changed.." does not insure anything! It doesn't even insure that it really is what you say it is, namely;, "...correctly designed ...tested process which is locked and cannot be accidentally changed..."

! as a business person, I can reasonably say that IT people are guilty of same same crimes you elude to! My bottom line is, both spreadsheets and database software have their rightful place in business and in government!
Apache OpenOffice 4.1.7, running on Windows 10
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Database or Spreadsheet ?

Post by John_Ha »

Villeroy.

Don't answer this too quickly as you would not want to get it wrong.

You are riding a motorcycle in a straight line. You want to turn left. What direction do you turn the handlebars?
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.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Database or Spreadsheet ?

Post by John_Ha »

HaroldLMNJ wrote:Your last post smacks with a great deal of cynicism, regarding users of spreadsheets and non IT professionals in general!
May I suggest you re-read (should that be read?) my first post in this thread in response to StanH's question.

I am not against spreadsheets - merely against improper use of spreadsheets by users who do not understand the risks they are taking.
John_Ha wrote:For simple stuff a spreadsheet is fine. For complicated stuff and especially when you want add new function or change the design a database is much better as the concept is different: pour the data in to the database and keep it safe. Now write separate stuff to analyse the data. There is no risk of processing altering the data unless you decide to alter the data.
And, of course, my attitude is based on the fact that a survey showed 88% of company spreadsheets have errors in them.

Actuarial firms typically disable the spreadsheet sort function and replace it with one written by IT professionals which is more robust and which protects the user from sorting when it will corrupt the data.
John_Ha wrote:See Spreadsheet mistakes - news stories for loads of horror stories - from Harvard reports, a $2billion fraud and countless errors of tens or hundreds of millions.
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
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database or Spreadsheet ?

Post by Villeroy »

John_Ha wrote:Villeroy.

Don't answer this too quickly as you would not want to get it wrong.

You are riding a motorcycle in a straight line. You want to turn left. What direction do you turn the handlebars?
In order to "initialize" a left turn, I push the left handle forward in order to shift the balance point inwards.
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: Database or Spreadsheet ?

Post by John_Ha »

Correct :super:

It's called counter steering and 99% of cyclists and motorcyclists don't realise they are doing it.

By turning the handlebars to the right, the front wheel goes an inch or two to the right which is enough to cause the bike to "fall away from the right turn which is leaning into the required left turn". When the bike is leaning how much you want for the left turn you straighten the bars and carry on doing the left turn. See http://www.obairlann.net/reaper/motorcy ... ering.html. Keep your eyes on where the tyre contacts the ground and you can see quite clearly how he steers the opposite way first. The front wheel travels several inches the "wrong" way, and then the bike leans into the turn and turns the front wheel into the turn.

My father was a motorcycle rider despatch rider during the war and once said "steering a motorcycle and steering a motorcycle and sidecar combination are completely different". It was only years later when I discovered counter steering I understood what he meant.
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
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database or Spreadsheet ?

Post by Villeroy »

John_Ha wrote:My father was a motorcycle rider despatch rider during the war.
Despatch rider was the wildest job I ever earned my living with shortly after the cold war when the streets of Berlin were the place to be.
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Database or Spreadsheet ?

Post by Lupp »

John_Ha wrote:My father was a motorcycle despatch rider during the war.
So was mine. He was killed, but not by the bike, I would assume.
Villeroy wrote:In order to "initialize" a left turn, I push the left handle forward in order to shift the balance point inwards.
That's what I told a lawyer involved in a litigation after a minor accident. He told me otherwise. Specialist lawyers surely know.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply