[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [libreoffice-users] Re: Was Re: Date will not format or sort when imported into calc (ods) - Databases vs Spreadsheets


On 09/09/2012 06:51 PM, JAMES MAJESKI wrote:
In a spreadsheet I get all the information I require and it is easy to get
additional information if needed. I can add, modify, and delete headers both
horizontally and vertically. I can add, modify, or delete cells, rows, or
columns at will. As long as I leave the original data alone, I can add as
many calculated columns or rows as I wish. One of my spreadsheets is a
complete double entry bookkeeping system. I can use split or freeze to keep
the column and row titles visible while scrolling through the data of the
various accounts.
According to what I read last night, you must be very careful or you may
compromise essential data. The implication was that it would be easier and
safer to create a new database than to modify it. From the
https://wiki.documentfoundation.org/Documentation/Publications documentation
:
"Fields can be added or deleted, but adding a field requires you to enter
the data for that one field for every existing record with an entry for that
field."
"Deleting a field deletes all the data once contained in that field."
"Changing the field type of a field can lead to data being lost either
partially or completely."
"Deleting a table removes all of the data contained in every field of the
table."
I will continue reading, but so far it seems that a spreadsheet has all the
advantages and none of the disadvantages of a database.
My knowledge of databases is very limited. If there is any advantage in
using a database over a spreadsheet, please share it with me
It seems to me that there ought to be advantages to using databases. After all, there are college courses of databases. Oracle has a database program (better known as a database management system that is very complex). The text book for it is 700+ pages. This is used in many of the large businesses. This textbook is known to give students headaches. So, is it possible that the things you see as advantages and disadvantages just might not be the whole story?
The following is the link to the draft folder for Base. You can download the database use with Chapter 1 of the Base Guide. There is also a link to the database (Budget.odb) used for Chapters 2-4 of the Base Guide. Perhaps the latter could show the advantages of using a database instead of a spreadsheet.

http://www.odfauthors.org/libreoffice/english/base-guide/draft-lo3.4

In the past, I have used a spreadsheet to keep track of my finances. Now I use a database which I prefer. Why? Size is one of the reasons. Each sheet get physically larger as data is entered, a database does not. Spreadsheet: data is entered into individual sheets based upon the specific data. Database: the entries are made in a single form. Spreadsheet: data must be linked from one sheet to another (I had links between data with a sheet). Database: This is done using table and field names.
To me, one of many advantages of a database is the query. You can tell it to get data from specific fields, and it will display it in a table format. This does not take up physical space like it would in a spreadsheet. Another is what can be done with the "simple" sum function in a query. For example, my financial database has a field named, Amount. In this field I enter all of the expenses and income that I have. I create the query to find the sum for the Amount field. I also tell the query to separate this sum so that I get a subtotal for each of my Accounts (I have 9). So I now have 9 subtotals of the Amount field. I also tell the query to list the names in the Accounts field alphabetically. In the end, the query's output is 2 columns by 9 columns: first column contains the names of the accounts listed alphabetically, and the second column contains the balance for each account listed.
This is the SQL statement for the query in Budget.odb (it only has 5 accounts):

SELECT "Account", SUM( "Amount" ) AS "Account Balance" FROM "Data" WHERE "Account" IS NOT NULL GROUP BY "Account" ORDER BY "Account"

This is the output:

Account



Account Balance

Bank of America



-600

Bank of Tennessee



-541

Cash



20.48

Wamu MasterCard



649.84

Wells Fargo VISA



218.45

I use the same structure for the balances of my accounts. The only difference is that in Budget.odb, the Account field has 5 entries (hence 5 rows of subtotals) whereas mine has 9 entries (hence 9 rows of subtotals).
It just occurred to me another advantage: the Data Source window which you open in Writer or Calc using the F4 key. You can see the query output without opening the database.

--Dan


--
For unsubscribe instructions e-mail to: users+help@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

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.