Database for community organisation

I belong to a community organisation (which means we don't have much money)
that requires a database.

The subject is a large community garden, we need to keep track of what was
planted, what it cost, who did how much work and what the produce sold for
etc. It will not be very large in terms of volume of data, nor require
great speed nor will there be many concurrent users. It does need to have a
user friendly interface and to be reliable (as we must account for our money
strictly) and preferably be able to operate on Windows and a Mac. The
chances of getting something suitable off the shelf are remote but I am
looking, meanwhile it looks like we have to build it.

In a previous life (I am retired now) I built bespoke small business systems
using SQLserver, Access, VBA, VB etc. However it is now some 9 years since
I got out the business so I am not up to date by any means. Access doesn't
run on a Mac and could have horrid deployment issues (which I don't suppose
have been fixed by MS in later versions). As I don't have control of the
configuration of the machines I want a robust simple installation process.
A laptop could be taken into the field to enter data and a wired network is
out of the question so I guess we are looking at batch
synchronisation/replication between copies whenever the machines are 'home'
or an internet connection with some nodes being on wireless. The latter is
feasible as wireless broadband is in the region.

So the first question is what database will do what I want? I posted here
because MySQL is a candidate. We need a relational database engine and
database management/admin capacity. It seems to me MySQL has these and
suits the distributed nature of the project, is all that correct?

I gather that MySQL has good connectivity so in principle it will sit behind
just about anything. Writing a front end from scratch in C++, VB etc is
not attractive because of the time it takes. Is there such a thing as a
rapid development graphical front end (similar to Access) that is a good fit
for MySQL that is also multiplatform? If not what is my best alternative
given we don't have much money?


David

David Hare-Scott:

I belong to a community organisation (which means we don't have much
money) that requires a database.

Hi David,

You are looking for something that allows you to store garden
deployment data. MySQL would be a candidate, but there is no
off-the-shelf client available, as far as I know.

However:

The subject is a large community garden, we need to keep track of
what was planted, what it cost, who did how much work and what the
produce sold for etc. It will not be very large in terms of volume
of data, nor require great speed nor will there be many concurrent
users.

This doesn't sound as if you would absolutely need a database.
Without knowing enough of your data and the data entry processes, my
gut says, that you might already benefit from an Excel spreadsheet for
the core data and a time reporting tool for the work done on the
projects.

Perhaps you could mention some issues that could not be solved with
this combination, so c.d.m denizens can look a bit deeper into your
needs.

... and preferably be able to operate on Windows and a Mac.

This shouts for a web based solution, though. In that case, your
application might be MySQL based, hosted on a cheap provider's server
(max $70 per year). I suppose the front end should be custom made, but
doesn't sound too complicated. Perhaps you can find a smart student
with knowledge of PHP to build the application for a small fee.
Of course, if this application is critical and you want to have it
serviced when needed, this is not a good idea.

If not what is my best alternative given we don't have much money?

What exactly are you willing to spend?
My first ballpark guess of the cost, based on your short description,
would be $1000 initially (to have the front end developed in PHP by a
willing whizzkid, or $2500 by the web developer around the corner) and
$500 each year.

The Excel/TimeSheet-solution might cost much less, but it would
probably be a single user system, so data from different sources should
be sent in to the operator by other means (e-mail?).


--
Erick

On 13 Sep 2011 in comp.databases.mysql, Erick T. Barkhuis wrote:

that you might already benefit from an Excel spreadsheet

OpenOffice.org Calc (http://openoffice.org/) and LibreOffice Calc
(http://www.libreoffice.org/) are free, work pretty much like Excel, and
handle Excel sheets well. They read but don't write .xlsx sheets.

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe
Usenet Improvement Project: http://twovoyagers.com/improve-usenet.org/

Thanks for your reply Erick

You are looking for something that allows you to store garden
deployment data. MySQL would be a candidate, but there is no
off-the-shelf client available, as far as I know.

However:

The subject is a large community garden, we need to keep track of
what was planted, what it cost, who did how much work and what the
produce sold for etc. It will not be very large in terms of volume
of data, nor require great speed nor will there be many concurrent
users.

This doesn't sound as if you would absolutely need a database.
Without knowing enough of your data and the data entry processes, my
gut says, that you might already benefit from an Excel spreadsheet for
the core data and a time reporting tool for the work done on the
projects.

Some members have already done some aspects of the work in spreadsheets.
For example the marketing is covered by spreadsheets where each workbook
represents a market day that has many sheets, each sheet is one vendor's
produce at a given market day. Trying to summarise produce sold across
days (for example) is possible, trying to summarise one vendor selected at
random across days is more difficult and so on.... debugging complex
spreadsheets is no fun. The more cross-linking there is the more fragile
the system becomes, you can get the situation where adding another row
incautiously in one sheet can break the system.

As the scope is expanded spreadsheets will become slower, more clunky and
inflexible. The scope for data validation and maintaining data integrity is
very limited and generalised reporting from spreadsheets is difficult. I am
certain that as time goes on the Management or funding authorities will find
there are some reports that they absolutely must have that were not
specified at the start.

This version needs to do much more and to be flexible, I skimmed over the
functions for brevity and because not all such requirements have yet been
defined. My WAG is the complete system will be 5-7 times more complex than
the marketing component. I think doing it in Excel would be a nightmare.

Perhaps you could mention some issues that could not be solved with
this combination, so c.d.m denizens can look a bit deeper into your
needs.



... and preferably be able to operate on Windows and a Mac.

It now seems that this requirement might be relaxed. As usual most users
are on Windows with a minority on Macs. It is possible that the Mac users
don't need access to all the data or to do updates, they could be passed
specific flat-file extracts of data in a compatible format or just reports.
I am running this down at present and I don't intend to start anything until
it is sorted out.

If the system can be run on Windows only, is there a suitable non web-based
solution for building front ends that is a good fit for MySQL?

This shouts for a web based solution, though. In that case, your
application might be MySQL based, hosted on a cheap provider's server
(max $70 per year). I suppose the front end should be custom made, but
doesn't sound too complicated. Perhaps you can find a smart student
with knowledge of PHP to build the application for a small fee.
Of course, if this application is critical and you want to have it
serviced when needed, this is not a good idea.

See below.

If not what is my best alternative given we don't have much money?

What exactly are you willing to spend?

Hopefully nothing. This is not a commercial venture, if it was compared to
such you would say it is seriously under-capitalised. We run on the
goodwill of the community, the efforts of members and a little public money
that is already earmarked for basics like seeds, watering systems etc. You
may say this is impossible, well it might be but people face this every day
in volunteer land.

My first ballpark guess of the cost, based on your short description,
would be $1000 initially (to have the front end developed in PHP by a
willing whizzkid, or $2500 by the web developer around the corner) and
$500 each year.

I am aware of the foolishness of not allowing for maintenance in the cost of
a system. But to get future funding the pilot has to succeed *and* we have
to report reliably on what we have done. To do that we need a system ASAP.
So we have to make a start and pull ourselves up by our bootstraps without
having any idea where future maintenance funds might come from.

David

David Hare-Scott wrote:

... and preferably be able to operate on Windows and a Mac.


It now seems that this requirement might be relaxed. As usual most users are
on Windows with a minority on Macs. It is possible that the Mac users don't
need access to all the data or to do updates, they could be passed specific
flat-file extracts of data in a compatible format or just reports. I am
running this down at present and I don't intend to start anything until it is
sorted out.

If the system can be run on Windows only, is there a suitable non web-based
solution for building front ends that is a good fit for MySQL?

You could try and see if a work tracking web application may have all the
elements you need or make your own search query and see what other projects
freshmeat has:

http://freshmeat.net/search?q=work+t...&submit=Search

--

//Aho