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?"
It seems like the work flow (what's the right term here) is out of whack there. Database projects that "lose interest before they become complex or important enough to warrant the IT department getting involved"? It reminds me of the commercial where they discover they don't have any computer problems so they can refocus on the real purpose of the company.
"It has got to be easy to use, web enabled, capable of storing documents and pictures and offer user level security."
Is it hard to set up an office webserver with some sort of content management that everyone can use?
A relational db is one thing, a document collaboration tool is another. If it is a MS Office environment, get someone who knows Sharepoint to come out and show you and one of your power users what it can do. You can even buy/build modular web parts if your document needs are out of the ordinary.
You'll need MSSQL on the backend, so that solves your "bigger than Access" problem right there. These tools dominate their markets for a reason.
FileMaker seems to be the easiest for non-techies to grasp, and supports image storage, publishing to web servers, and other goodies they want. Also hooks to SQL if you need more horsepower on the backend.
Never thought I would say this, but if documents, images, and security for a web site are your main consern: Lotus Notes.
Easy to use with a little bit of training, and works wonders with documents (suppost to be better at it than sharepoint)
Bad User. No biscuit!
I've used FileMaker quite successfully for many years. It is simple enough for most folk, but extensible. It can store pictures and other binary data. The web interface can be customized. User level access control is built-in. It runs under Windows and Mac and in Wine under Linux. Databases can be migrated to a FileMaker Server, if they go beyond the standalone limits (10 simultaneous users, typically). There's also a compiler to create standalone applications from databases, without needing a license per user.
All in all, FileMaker is a great tool for this sort of thing.
-- Dan Jenkins, Rastech Inc.
"Most of the time they loose interest" As opposed to "tight interest"?
In a 200 person company, I would get rid of Access on the desktop. I see the appeal, but it's time for the IT department to step up and consolidate database development/maintenance so that it is more centralized.
Once IT takes control of all databases, all sorts of things fall into place, such as security, backups, moving to a single technology (SQL Server or MySQL), etc. At first it is a bit more costly and people will complain about losing flexibility. But in the lgng run, it is cheaper and people who do OTHER work will find it nicer to be able to focus on their core expertise.
"If you want to improve, be content to be thought foolish and stupid." - Epictetus
100% wrong on all counts!
.NET 2.0, SQL Server extensively, and looking forward to Vista - imagine that!) It has a easy mechanism to build "GUIs", but that's about it. The underlying DB itself is crap (and that's borderline insulting crap)
Access CAN store anything as blobs.
As for supporting the rest, you're wrong there too.
-Access security model is a total JOKE - and a bad one at that. No normal RDBMS security, just some shitty broken built-in sorry excuse for security (and shitty cmd line tool to recover it).
-It *doesn't* scale. At all. This is thoroughly documented (you'd know if you had even read a FAQ or something). It wasn't EVER meant to be used for more than a handful of users (MS KB even state this). Put a few concurrent users and see for yourself. You'll be wishing for abysmal performance, as that'd be already a huge improvement (the underlying jet/dao-era tech sucks hard)
-access DBs are really inefficient - they use up FAR too much network bandwidth (making it slow for everyone else), are slow, and aren't even reliable over network links - expect your files to become corrupted every now and then.
-it doesn't use vbscript, but rather VBA. Another sucky poor excuse for an outdated sucky scriting language. Heck, even 10 years ago (well, with Office 97) it sucked. Bad enough that you'd even wish for PHP instead (and that's saying a lot). And VSTO is too complicated for simple things. Because you manage to script stuff in a spaghetti manner using world's poorest scripting language doesn't mean Access has the features in the first place.
Access is the single and ONLY worst DB than MySQL (It's bad enough that I could make a 600 page rant about it -it's by FAR MS' worst product - heck, I'd rather admit I use MS Bob and love the office paperclip thing and search assistant dog!). And that's coming from what most ppl here would call a "Microsoft Shill" and fanboy (C# coder, using
He'd be better off with a *REAL* RDBMS, be it MSSQL, Oracle, DB2, PostgreSQL, Firebird, and even MySQL (don't like it much, but *anything* is a step up from Access, in terms of security, scalability, performance, availability, features, etc).
What he's looking for isn't so much a database itself anyways. It's something to create "GUIs" with it. Things like ASPMaker/PHPMaker/whatever from http://hkvstore.com/ or such that'll easily and quickly create a simple web front end for the various DBs. If more time/budget permits, then yes by all means use code generation & ORM tools to create a quality, well made app instead (the generated ones aren't exactly the best, but it takes minutes to create the thing, and it's almost free)
Now, y'all go ahead and mod me -1, Flamebait because you know it's true.
Why not use something like TWiki? It can store those things plus it has decent enough access control. We've moved almost our entire business unit (around 600 users) web content and migrated a lot of processes to one centralized TWiki installation running on a Solaris box and couldn't be happier.
... 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
I've been kicking around building an OSS database utility to compete with the likes of Access and Toad. It would be based on PHP, SqLite, and ODBC. However, I cannot find a decent open-source JavaScript editable data-grid control. They tend to have one big flaw or another. Maybe in another year such will finally mature. Data-grids are a must for such a util.
Table-ized A.I.
For 200 users, with user-level security, you just need to find a tech willing to actually spend the time to make Access work. 2007 has plenty of additional gizmos, incluing a new "attachment" data type to, well, store those documetns you can't really store in Access.
(You can store Images in Access. You use the "image" file type.)
Now, if you just want to upgrade their database, the SINGLE CHEAPEST thing you can do is setup SQL Server 2005 Express. Access can upgrade itself to use the server (Use the "SQL Database Engine" if you're version-shy), and you gain all of those things that you don't have now.
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
Pick whatever database backend you like; big, centrally managed, scalable, as complex as you like. IT manages it; handles the schema and maintenance, necessary stored procedures. A professional data architect in the IT department has final say on the architecture, but works with the requesting parties to ensure that it can fill their data needs. If necessary, views or similar can be constructed and made available on top of the actual data structure to make it easier for the non-programmers to interact with. You then expose, with appropriately restricted permissions, the database server to all these people with their small pet database projects. You know that most of them are going to be looking at most of the same basic table structure--they need names, phone numbers, whatever. It's a decent bet, since they're in the same company, that they're actually going to be storing the same data, no less. Let them at it--give 'em ODBC connections and turn 'em loose.
They do the work; you give some input and assistance, but don't turn any of them into full-blown development projects. All you have to do is manage the backend. They get to scratch their itch, you get to look helpful and enabling, and no one gets sucked into big, expensive tools or projects.
No relation to Happy Monkey
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."
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
and - for instance - Plone, too: It ;-)
- runs on almost everything (Windows, Mac OSX, Linux, *BSD - from Servers to Laptops),
- is very easy to set up and maintain, - has an easy-to-understand web-based user interface,
- has a simple but powerful user management
- can store data in almost any SQL database, but
- comes with its own, very powerful object-oriented DB (ZODB).
Especially the last point makes it appear "naturally" to many users: They can store data as they are used to do in their filesystem inside folders, documents etc. There is a LOT of additional, easy-to-use plug-ins (called "products") that allow, for instance, to put files onto the filesystem through-the-web -- and: all is very easily scriptable with Python.
So: Welcome to the Zope/Plone Community
Sounds like you need a content management system, not just a database. Your users basically seem to wish for a way to share project-related materials. I see you've already considered that...
...so I'd definitely be interested to hear what limitations you ran into there. It's highly possible that some of the open CMS systems (Drupal, etc) could offer you what Sharepoint doesn't, but it's hard to say without knowing exactly what parts of Sharepoint you found limiting for your needs.
You might also consider a hosted collaboration tool such as Basecamp. I haven't used it myself but it has quite a few fans. It's probably more limited (and certainly less extensible) than software like Drupal but the ease of administration (since it's hosted) and easy accessibility (since it's not on your LAN, it's on the 'net) could compensate. Then again, if you're the IT guy... perhaps you don't want a zero-administation solution for job security's sake. :)
OtakuBooty.com: Smart, funny, sexy nerds.
Migrating from Access to SQL would be logical for you. Clients keep using Access and its forms to access the data, so they keep the application that they developed, and you get managebility, proper multi-user and access permissions.
It will be almost transparent for users.
Ive used postgres ODBC with Access ( just expremental, we have real MicroSoft SQL licenses ) and it seemed to do fine.
Using the native jet database is bad anyway, as you mentioned.
---- Booth was a patriot ----