Slashdot Mirror
A Database for the Office?
travellerjohn asks: "I work in a small company (200 people in 7 offices), where the staff uses Microsoft Access to create various databases. Most of the time they lose interest before the databases become complex or important enough to warrant the IT department getting involved. However, from time to time, someone turns up at our door looking for help with their pet project, often starting with statements like 'it should work over the intranet' or questions like 'why can't it store documents and pictures?' or 'how do I control user access?' When we sit them down and explain how much it will cost to rewrite their database in PHP/VB/JSP, or whatever we sound unhelpful and expensive. What database tool does Slashdot recommend I provide our staff? It has got to be easy to use, web enabled, capable of storing documents and pictures and offer user level security. We have tried Sharepoint with some success but that is pretty limited, too, and I have looked at Oracle Application Express. Open source would be good, but I would pay for the right product. Any suggestions?"
I think what you want is a CMS system like Metadot, not a database. If users are looking for an easy way to share images and documents, a database really isn't the best solution. Hell, a shared network drive would be better than a DB.
... BAN Access. One day the database "created just for a simple task" may become the repository of mission critical business data. Access is inappropriate and incompetent to the task of being a "database" in any meaningful sense of the word.
Training is critical; ensure staff recieve spreadsheet (excel or your chosen open source brew) training in reasonable depth... Then encourage them to use spreadsheets for "simple tasks" involving data storeage. Making some "standard" macros for query dialogs is useful here. Then if the data does become important, it is a trivial task to move it into a real database (unlike access!).
One solution I have seen effectively used is the creation of a "general" database using mysql and a rather clever PHP front end. The database allowed for 8 "fields"; each field was really three fields, Data descriptor, Data name and Data type. Essentially the ID-10-T entered a name for the data field, its data and selected a type from a drop down box. They could select previous "name and type" combinations they had used. This then spawns a copy of this "standard" database with user access privelges set to a default rule; another interface allowed advanced users to adjust this. Finally a generic PHP gateway presented them a data entry/query sheet that formatted itself based on type... Sure, it was probably alot of work, once; but it ensured that all future databases created were in "real" databases that were relatively easy to maintain for the IT department.
Essentially, my suggestion is to encourage them to work with excel or similar with a few standard macros/dialogs created to allow data entry and search to be "simple" (small up front work by IT, maintenance required); or create a more complex "standardised" database and access system (alot of up front effort, minimal maintenance). This trades effort for ease of future scaleability and maintenance.
Just my $0.02
err!
jak
Sorry. I suppose I could have elaborated a little further.
:)
Axis is a collection of 3 projects:
- Gtk2::Ex::DBI ( forms )
- Gtk2::Ex::Datasheet ( datasheets )
- PDF::ReportWriter ( reports )
They're all cross-platform ( heavyily tested under Linux and Windows 2000 ) and open-source.
The basic idea is that you create your GUI in Glade ( ie Gtk2 ). You then create a Gtk2::Ex::DBI object, pass it your Glade XML file, and it will connect to the table you specify, and 'bind' all the widgets in your Glade XML file with a name that matches a fieldname in the table.
The datasheet module is similar, but instead of creating a GUI and laying out widgets and such, everything goes into a treeview ( datasheet ).
PDF::ReportWriter makes high-quality reports from XML report definitions. It supports unlimited grouping, group functions such as sum, count, etc, intelligent page breaking, page headers & footers, and a WHOLE lot more.
There are plentiful screenshots on the website. All modules are under active development ( ie right now ). All feature requests, bug reports and patches welcome. Check it out
http://entropy.homelinux.org/axis_not_evil
Sort of. You can embed things like word documents, then launch them from the Access app, but Access does some weird stuff with filesizes. Expect to see even simple documents consume an additional half a meg once they're added, and big documents to chew up even more space.
"I've got more toys than Teruhisa Kitahara."
An aside -- but whats your problem with mysql? I use My daily, and while I've got some issues, I never thought it to be as bad as you claim... -Riz
The biggest issue is that Access has a ~2GB file limitation so storing large things in it is not a good idea. Secondly, JET is well and truly dead and unsupported and doesn't support things you really might need for multi-user system such as referential integrity, triggers, stored procs.
MS provide a SQL Server Express 2005 with a 4Gb DB limit for free that would be a good option. Oracle has something similar. There is also PostgreSQL that has no limits. The last option has the benefit of being totally unencumbered, but I don't believe the ODBC or .NET drivers are quite as polished as the other options.
With SharePoint 2007 there should be tighter integration as well and it will cover ALL of these requests.
I admit SP doesn't look like much now, but as you dig and see the Object Model, how it can be integrated into user environments, workspaces, audiences, etc. - it's quite powerful.
No, I'm not blindly pro-MicroSoft and they've still got a long way to go (apparently SP07 slices bread, too) it's just that I can see how MicroSoft is going and it's going to clean Linuxes clock for collaboration.
Coincidentally this will happen just as Linux is finally ready for the desktop.
The opposite of progress is congress