Re: [libreoffice-users] Re: database or spreadsheet

On 02/07/2012 04:18 PM, Andreas Säger wrote:
> Am 07.02.2012 21:57, Jay Lozier wrote:
> > The irony is that most SQL queries are easier to understand because they
> > reference tables and columns by name (out of necessity) and usually have
> > a selection criteria that is makes contextual sense, such as vendor
> > name. The above is not easy to follow or debug, I know because I do
> > similar in the past.
> >
> > SELECT column names
> > FROM table
> > WHERE criteria for selection
> >
> > is easier to follow, even when the query is much more complex
>
> SQL is much closer to human language than spreadsheet formulas. There 
> is no need to test for missing/invalid data since a database does not 
> allow for missing, invalid or inconsistent data. If there is a 
> relation, the look up in the detail table can not fail since you can 
> not use any references to non existing items nor can you delete any 
> items that are referred in other tables.
> Databases provide much better functionality for text values where Calc 
> does it all wrong.
>
> =IF(A1="";"";IF(AND(ISNUMBER(A1);A1>0);ROUND(A1;2)*VLOOKUP(A1;TaxRates;2)) 
> [and this bullshit has to be copied down to each and every row of the 
> ever growing sales list].
>
> If a database record requires a product price then there can not be an 
> empty value nor text in the price column. If that price is defined as 
> a number between 0.01 and 9,999.99 there won't be any value with more 
> than 2 digits behind the comma and all calculations will use that 
> level of precision without explicit rounding. And a user can not 
> override this "validation" with a simple copy&paste. Nobody has to 
> copy any calculation formula. They are defined by the database 
> designer for the entire field. A spreadsheet has no records nor 
> fields, not even tables. Tables with records and fields are only in 
> the head of the user when he uses rectangles of cells as if they were 
> organized like this. In fact you can use a sheet of cells beyond these 
> rigid concepts.
The comment " Tables with records and fields are only in the head of the 
user when he uses rectangles of cells as if they were organized like 
this. In fact you can use a sheet of cells beyond these rigid concepts." 
is often overlooked. Often another can override the implicit design of a 
spreadsheet. Spreadsheets do have tools to limit this but they must be 
activated by the designer.

Another flaw with spreadsheet database is that it is difficult to make 
sure the user enters all the proper data while a database will have 
implicit rules embedded for each table for checking that all the data is 
entered. If a table row has 8 elements and requires 5 must have data, 
this is can set in database table design but not so easily with a 
spreadsheet.

--
Jay Lozier
jslozier@gmail.com


--
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