[Haifux] FOSS alternatives to an Access-based organizational software solution

[guy keren]

as a non-expert in this area, i will try to give you two cents and a 
penny ;)

1. regarding scaling the existing solution - i guess that switching the 
back-end to using SQL server will make it scale better. further, it 
might be possible to make a crude test of this without changing the 
entire application - i think i remember that access can use SQL server 
as a backend. there used to be a free version of SQL server (that was 
limited to 10 users) - which may be used in such a "proof of concept" 
test. it used to be called MSDE. these days it is simply the "express 
edition" of sql server 2005 or sql server 2008. if you live with the 
limitations it imposes - you might be able to use the free version also 
in production:

     http://www.microsoft.com/express/Database/

2. did you verify that you don't need to simply upgrade the server 
hardware? or do you also see the slow down on the client machines?

3. i imagine that if you build such a thing today, making it web-based 
will make more sense then writing a full-fledged application client. 
especially if you'll use a linux back-end - since you'll want the 
clients to work on windows. with today's technology, web applications 
can give you a good frontend (with use of javascript and ajax) to show 
data records (e.g. look at google's online spreadsheet application). 
of-course, it'll be a good idea to define the desired interface first, 
and see how much data you want to display at any given time, and make a 
small proof of concept to see that the javascript code in the browser 
handles it well (javascript is somewhat slow - and i'm not sure if it'll 
work to show thousands of records on a single screen - so you need to 
figure out if you need to be able to do this on the client - or whether 
viewing by "screens" (show first 100, show next 100, etc.) will be good 
for you when showing query results.

4. i would suggest that you subscribe to the linux-il mailing list - 
there are many more people there, and in particular - people that work 
with LAMP for their living - they are likely to be able to give you 
better and more specific advice:

     http://www.hamakor.org.il/mailing-lists/linux-il.html

keep in mind that some of them are consultants and developers that 
provide the development services you are looking for - so they can help 
you better (and on the other hand - they may be biased towards their 
preferred solutions).

--guy

Eyal Rozenberg wrote:
> 
> Hello all,
> 
> I would like some advice regarding a possible upgrade of an 
> organizational software application we use at the Graduate Student 
> Organization.
> 
> In fact, while initially what I need is advice and directions, we may 
> soon be interested in contracting a single developer or a (small?) 
> development company to entirely replace our existing system with 
> something nice and FOSSy.
> 
> To describe things briefly, our system:
> 
> - Keeps grad student personal data.
> - Records payments and debts.
> - Communicates directly or via imported/exported data files with some 
> Technion and non-Technion systems: The listserv, ANAM, the student 
> tuition people etc.
> - Records non-financial operations such as collecting a gift, 
> joining/leaving the organization etc.
> - Is used simultaneously by more than one person on a network (although 
> it is extremely rare for two people to try to add or modify db records 
> at the same time)
> 
> The number of people handled by the system at any given time can range 
> upto 5,000 (let's make it 10,000 to be on the safe side), and if we keep 
> info about people active in the past and don't only maintain a snapshot 
> of the present, then we need to be able to handle, say, 10,000 as a real 
> estimate for the next several years and 30,000 to be on the safe side. 
> There isn't any heavy calculation going on, it's all pretty routine and 
> mundane.
> 
> Before talking about our currently operating solution, here are some 
> questions:
> 
> Q1: What software platforms/toolkits/etc. would you recommend for this 
> kind of a system? Be specific, not "make it LAMP-based".
> Q2: Do you know of specific software apps, already written, which cover 
> this functionality and may be easily adapted to our needs (or would not 
> need any adapting)?
> Q3: Do you know people/organizations who run such systems with FOSS 
> solutions, and would be willing to share their experiences?
> 
> Our system as you may probably have guessed is based on MS Access, with 
> a Frontend-Backend split to ease multi-user use. While it is working 
> well enough today, it is an endless patch-work, not well documented, 
> without proper specs for anything, and showing signs of aging with every 
> operation becoming slower as features are added and the number of people 
> grows. There are also some foundational architectural assumptions which 
> we want to change (e.g. the present snapshot vs. full history I 
> mentioned above).
> 
> Q4: Not a list-relevant question, but are more recent versions of 
> Access, or Access + a full-blown SQL server, options which allow better 
> scaling? Perhaps with careful coding?
> Q5: Does any of you know people we could consult regarding migrating 
> away from MS-Access to a more capable platform which is both FOSS and is 
> easy to extend in the way Access-based apps are?
> Q6: Is it or is it not worth thinking in the direction of web-based 
> frontends to databases, rather than plain vanilla apps?
> 
> 
> Any other thoughts/comments are welcome.
> 
> Eyal
> 
> PS - We're an organization of mostly non-Technically-oriented people, 
> bear that in mind.
> _______________________________________________
> Haifux mailing list
> Haifux at haifux.org
> http://hamakor.org.il/cgi-bin/mailman/listinfo/haifux