Re: [libreoffice-users] Large spreadsheet does not open in LibreOffice.

On 14/11/2015 06:42, James E. Lang wrote:

> I have no clue how to design and implement a database to replace the mess.

One of the good things about Microsoft Access, was that it was easy for
people who knew nothing about databases, to create their own database.
I'll grant, that from a theoretical point of view, the overwhelming
majority of those databases are non-optimal.  For the typical
SOHO/individual user database, that is completely irrelevant.

Where LibO falls down, is that database creation is neither intuitive,
nor well explained.

Your basic relational database record has four fields:
* Record number;
* A value;
* What that value represents;
* What that value is associated with;

The record number is to ensure that every record has at least one unique
field. This way, data can be edited, deleted, or added, as required. (If
the database software one uses does not mandate/require that at least
one field in each record be unique, then the database software is trash,
and its usage should be permanently barred, ASAP.)

The value that is represented is the piece of information in the record
that is important. The client name, or the client address, for example.

What the value represents, tells the user what that piece of information
is.  It isn't much use have a "value represented" of 48.6,122.25, if one
has no idea what that value is supposed to be of.

What the value is associated with, indicates which other records share
common parameters with this specific record.  for example, "48.6,
122.25" is part of my address.  Other records would contain the street
number, street name, city name, Postal Code, and Country.

What makes database construction awkward, is in knowing which records
belong together. To make that task easier, instead of having a
four-field record, one has a record with umpteen fields.

IE:
Address Database record:
* Record number;
* Name;
* Street Number;
* Street Name;
* Street Extension;
* City;
* Postal Code;
* Country;
instead of 7 records, each with only one part of the address, which is
what relational database theory suggests is optimum;

> I suspect that most users who have these oversized, abused,
spreadsheets have the same problem.

Yes, and no.

_Lotus 1,2,3_ literally revolutionized how data could be understood, and
analysed. Things that formerly took months, if not years to determine,
could now be done in weeks. The major hold up was being able to use the
corporate database. What the more daring users did, was simply added the
data that would have gone into the corporate database, onto a quick and
dirty spreadsheet. These spreadsheets were then spread around the
office, and quickly became mission-critical monstrosities, entirely
unsupported by IT.

Circa 2000, one organization that specialized in corporate software
development estimated that it would take five years for the typical
corporate mission-critical spreadsheet that had become a monstrosity, to
be properly developed by the corporate IT department. For most
organizations, that was an unacceptable length of time to wait for a
replacement, especially since the odds were that the spreadsheet had
originated less than five years earlier.

Slowly, spreadsheet developers incorporated quasi-database functionality
into their spreadsheets, to the point that spreadsheets that lacked
database functionality, were considered to be "crippled" by
"power-users".

The end result is that one can currently find books exonerating the
usage of spreadsheet software as databases, and, worse luck, teaching
users how to abuse the spreadsheet software, as a database program.
(I've forgotten the name of the book on using Excel, but it was a fairly
detailed explanation of how to use Excel as a database.  Needless to
say, it would have been much easier, and safer (^1), for the user to
have done everything described in that book, by using R, or even Python,
and SQLite, MariaDB, or PostgreSQL.)


All that said, some database developers use spreadsheets, to determine
what fields are needed, and what the database flow should allow, and
what it should dis-allow.IOW, the spreadsheet becomes a spec-sheet for
the database, and attendant items, that they deliver.

> So I appear to be stuck. I won't go into any of my spreadsheet's
details here.

This is where a dozen or more database templates, with comprehensive
instructions on how to use them, and modify them, would be extremely useful.

> Then it will start over and I'll need to figure out how to carry over
some values computed from this year's data to the new spreadsheet.

In an ideal world, one of those database templates would work as a
replacement for your spreadsheet.


^1: More than one researcher has argued that The Human Genome Project
was a complete waste of money, time, and effort, because of the numerous
_known_ errors inflicted upon the project, by the use of Excel, and its
silent translation of data to something other that what the user wrote.
FWIW, Calc does the same type of silent translation. Had that project
used a real database program, those errors would not have happened.

jonathon


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted