A friend of mine works for the BC Recreation and Parks Association. As part of a new initiative, they want to create a database of members. Their requirements are quite low, but I have little to no experience in this area. Basically, they’re looking for a simple solution that’s got a user-friendly UI. It doesn’t necessarily need to be Web-based, or have a Web front-end, but that might be advantageous. There’s some of our email exchange after the jump [more]. Any suggestions?
1. What are you actually trying to achieve?
We’d like to set up a database for the Active Communities Initiative to
store information pertaining to contact info./registration, grant
applicants, workshops, etc. We’d like to be able to pull information/create
reports on the information we collect (e.g., which registered communities
haven’t taken workshops).2. Do you have an existing database?
Currently the organization I work for (BCRPA) has a database (Access) but
the initiative I manage is called Active Communities and it’s a separate
initiative delivered by BCRPA (i.e., we’re housed here) so we don’t use
their database. Currently we’re keeping registration and workshop info. in
Excel files but we’d like a more comprehensive management system.3. What’s your highest priority? Budget? Security? Ease of use? Scalability
(that is, will the database be growing a lot)?
From my perspective our highest priorities are usability and budget.
Scalability would be low – we don’t need a complex database because we won’t
have thousands of clients/registrants (so a relatively simple database would
do).4. Will you need to access the database over the Web?
As for web-based access – it’s
not a requirement because everything will be internal access. Participants
(clients) won’t access the database and data will be entered onsite in the
office, rather than from remote locations.
DarrenBarefoot.com 
Is it necessarily a Windows thing? Most good database solutions are on *nix, but there are Windows ports for a bunch.
Do they have anyone that’s good with databases? For schema design, etc.? By user friendly, does he mean a “wizard” that will walk him through the whole process?
Your friend seems to confuse scalability with complexity. If you have a million customers, they could still be stored in one simple table – if you have only 500 customers, but are storing a lot of data about each, you could have several tables with foreign key constraints, triggers, stored procedures, etc.
More info please 🙂
Chris: I’m not sure about their hosting environment, but I’d imagine that they’re flexible on Windows vs. Unix.
I’d say they probably don’t have anybody who’s good with databases, or they wouldn’t be asking me.
I’m guessing that we’re talking about a fairly simple data structure here, and (as she mentions) relatively few records.
Why not just utilize Outlook, or another CRM if it is for internal use only? The info could be exported at a later time, in a comma-delimited file, and the interface is user-friendly and easy to modify with custom fields as needed?
They could also import in the same manner from their current solution, Excel.
If they’ve already got an Access license, and maybe some of them have Access experience, that may be the way to go (as much as it pains me to say it). If he’s got a unix box and someone with at least a little tech savvy, mysql is pretty easy to set up, and there a few frontends that make things quite easy (phpmyadmin is good).
Netchick: I imagine it’s an information-sharing and/or versioning issue, as to why they don’t want to go with Excel.
Chris: Indeed, though I disagree that phpmyadmin is acceptable for use by normal humans (at least, not the versions I’ve seen).
This is precisely what Access was designed for. An experience person could probably set something up for them in an hour or two. An inexperienced person could do it in a day or two after reading a few help files. My advice? Find a starving CS student somewhere and they’ll do it happily (we used to kill for jobs like this when we were in first or second year).
Nothing written in response to the first question indicates that Excel is not good enough to do the job. Saying that you need a “comprehensive management system” is too vague. Maybe Excel is good enough and all they need is to put in place some manual procedures to address a few things that Excel won’t do. More details please.
Tradervik: I’m guessing Excel won’t fly because (as I understand it), it lacks version control. That is, if two people are modifying the document simultaneously–who wins?
Seeing as they are running Access that probably means they do not have a SQL server license. If you can get SQL server (or even MSDE) then it would be no problem to hack together a Windows client and/or ASP solution.
As Another Chris mentions though you can probably do this within Access itself if you can make do with a lightweight solution.
Let me know if you need more info if you’re thinking of going down the Windows route.
Access is really only meant to be used as a single-user database. If you start to get too many concurrent users, you could run into trouble with it. It might work for you, but I’ve heard and seen a lot of problems with speed and data corruption once you get several people using it.
I’d rather use PostgreSQL or MySQL if you want to support multiple connections. They’re both cross-platform and free.
But if you’re only going to have one or two people access it at the same time, you might as well stick with Access since it has some decent built-in reports and has database setup wizards.
I would recommend 4th Dimension (http://www.4d.com) very stable, lots of wizards, extremely secure, single-seat or client/server (easy no-conversion upgrade), and it has a webserver plugin (easy no-conversion upgrade). There is a large and active developer self-help community online.
PremiumSoft Navicat is a great GUI for mySQL — If they want a really budget system that can have multiple people accessing and updating, that would be what I recommend. It makes setting up the tables extremely simple, and not run by command line.