ever seen a spreadsheet-like database ?

[lalbatros]

I am considering a funny idea.
It started from a request to build a database in replacement of many small spreadsheets and a badly designed database.
The main requirement is flexibility.
I delayed this project by more than six months, but it could be fun now for the holidays.

If I want to keep things simple and aim at flexibility, I think I should forget about defining entities and specific tables.
So, I should forget about tables for stock levels, for materials, for chemical properties, ...
The best way to define what has to be done is the following:

numbers have to be collected or calculated every day,
calculations involve only current day and previous day data,
calculation formulas are user-defined data

This looks very much like a simplified kind of daily-based spreadsheet where formulas would refer to data on the same day or the day before. The formulas would be rather simple but could change from time to time. There could be also new formulas from time to time.

Basically, a record in the database I am thinking of would look like this:

(day, item, formula, value, responsible people, ...)

Have you ever seen a database designed to mimick a spreadsheet?
I would enjoy looking at that.

 

[xenou]

I think a lot depends on how the numbers are collected. In general, its nice to have clearly defined "raw data", Excel or Access. The requirement for a database comes down purely to the question of whether or not you have a need for relational entities or not.

1) if there's duplicated data (same thing being entered in a lot of places),
2) if there's repeating items (items in columns that say First Result, Second result, Third result, etc. - to where you'd have a problem if suddenly someone came up with Fourth result and you have no column for it!)
3) if there's data that is creating problems because it's spelled differently in different places
4) if there's just lots and lots of data (somewher over 20,000-30,000 rows and its time to start thinking about whether you should be using Excel)

Excel is indeed good for number-crunching - if your requirements include on the spot calculations and the data is able to be maintained in Excel, Excel could work nicely.


----------------------------------------------------
Some food for thought:

http://www.mrexcel.com/forum/showthread.php?t=310279

http://www.mrexcel.com/forum/showthread.php?t=294180&highlight=ADO+query+workbook&page=3

 

[xenou]

Oddly, enough I may have gotten your question backwards. But if I were to create a database to mimic a spreadsheet, I would use custom functions to imitate the use of formulas in Excel (i.e., where the cell contains more than just a single, simple formula) - this way, you'd be able to change the formulas when needed in a single easy to find place (in the custom function code).