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+unsubscr...@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