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