Spreadsheets Compared to Databases

[acolburn]

As a newbie I'm struggling with differences between databases and spreadsheets. My program will have strings and numbers displayed (and edited) in a spreadsheet-like grid. Data displayed in some cells will be the result of calculations made with data displayed in other cells.

At the same time, there will be master-detail like relationships--when users double-click some cells they'll be taken to a second grid displaying additional info (rows and columns).

Narrowly, I don't know how to figure out whether I should be using spreadsheet components for this, or DB-linked components. More generally, I'm unclear about when to code with spreadsheets vs flat file databases (e.g., SQLite, CSV). I recognize a spreadsheet is GUI & database is underlying data; maybe I should word the question in terms of choosing a data type, but examples with non-DB-linked spreadsheet components & db-linked components often seem to take totally different approaches.

Thank you for putting up with my ignorance :-)

[wp]

General rule: when you have the same data type all along each column then it is better to use a database table. Example: In column A (using spreadsheet notation) you have names, in column B you have cities --> a database.

On the other hand, if you have different data types spread all over the table it is a spreadsheet.

[LacaK]

Are "spreadsheet components" DB-aware ?
If not, then you can not use directly components like TDBGrid, TDBEdit for editing data.
In this case you are required to move data from  "spreadsheet components" to display/editing components like TCaption/TEdit and later after user edits them move data from these components back to "spreadsheet components".

[wp]

Are "spreadsheet components" DB-aware ?

No. BTW, data editing support by the fpspreadsheet visual components is still rudimentary and can drive a user crazy if he is used to working with Excel etc.

[JD]

As a newbie I'm struggling with differences between databases and spreadsheets. My program will have strings and numbers displayed (and edited) in a spreadsheet-like grid. Data displayed in some cells will be the result of calculations made with data displayed in other cells.

At the same time, there will be master-detail like relationships--when users double-click some cells they'll be taken to a second grid displaying additional info (rows and columns).

I actually wrote a contact management application with data entry forms (thank you John Walkenbach!) using VBA & Excel 2000 developer version many years ago (my boss wanted it that way - office politics). Parameters were saved in columns on a hidden spreadsheet and they were used to populate comboboxes, listviews etc. Excel's macro recorder was a life saver.

It worked; but maintenance, data validation & above all speed rapidly became a problem. I had experience with FoxPro & I knew about Visual Basic but I wanted another solution & that was where I discovered Delphi 6 & a very early version of Lazarus. I jumped ship & the rest is history.

The moral of this story for me is this

a) it could work for small amounts of data but when data gets very large speed becomes an issue
b) it is a nightmare in a multiuser environment
c) security is not watertight
d) master-detail relationships are way better in databases (try enforcing referential integrity in a spreadsheet app to understand my point)

I will go with a database over a spreadsheet any day. My advice - use a database. It is better, in my opinion, in the long run.

In addition, with the excellent FpSpreadsheet component, you can always export data to spreadsheets for further analysis which is how I do it now.

JD

[acolburn]

My thanks to each of you for responding ... Looks like I'll be making some databases!