Building and Maintaining Large, Collaborative Databases?

hherb asks: "We are in the process of building and maintaining a free pharmaceutical reference database, in order to liberate medical decision support systems from vendor driven databases. For that purpose, we need some way of allowing multiple authors to contribute to a large number of data records - most of them small...too small for CVS. We need version tracking as well as authentication of authors. We need to tag every bit of information enterrred with information about source reference, author, peer review result, and so forth. I had a look at existing version tracking software. like CVS and Subversion, and I did not have the impression that any of them would suit our needs. Does anyone have ideas for *free* software solutions that we can use?"

Comments

PostGreSQL

[stanwirth]

You might want to try an open-source relational database engine like PostGreSQL

This means, of course, you'll have to create your own schemata for logging version control and records added, but versioning additions to the database -- and user/connect information -- can be tracked with a combination of triggers and timestamps in a separate table. These are fairly standard techniques in generating an audit trail for tracking relational database changes and enforcing data integrity constraints. While the syntax varies from database engine to database engine--PostGreSQL is a good (free) place to start, and if the spirit moves you (or if the DB becomes too large) the syntax differences do not preclude your moving to, say, a development edition of IBM's DB2 UDB or even (aack!) Oracle. All three run rather nicely under SuSE Linux, and are said to run quite nicely under RedHat, as well.

It probably wouldn't harm you at all to develop this database with a relational database, and test it under different engines-- in fact, it would likely make you highly employable in the very near future.

What other functionality will you need?

[epatek]

It sounds to me like you need more of the Database (probably relational) than just a version management system. Since you mentioned of different authors working on the (potentially) same pieces of the information - you will need one with decent support for locking and transaction isolation levels. PostgreSQL is probably the right candidate from the OpenSource domain and from proprietary ones - I would say Oracle. You might need to look into some additional features such as how your database will be searched - will you just provide search by drug name (relatively simple, if everybody can provide the correct spelling for drug names) or you need something with richer functionality, such as text search in the drug description and/or reviews search of the contributing members. Again - Oracle will give you something called interMedia text search, which works OK. There are some projects in the Free Source world to provide similar functionality (inclduding in MySQL) - but I do not know the name(s) of them. Out of proprietary world for the special Text Search engines I can name Thunderstone and AltaVista - I worked with both, have their own pros and cons, but both are pretty expensive ;-). And... you still need to write the front end/application for functionality you need ;-).

Good Luck - sounds like an interesting project to do.

Bugzilla

[The Bungi]

Some people have recommended a Wiki... I'd go with Bugzilla. Maybe with a little bit of Perl hacking you can modify it so that it looks (and behaves) the way you want.

I have a modified version of Bugzilla running on Windows (W2KAS/IIS) that is being tested for something vaguely similar. It works great. The hard part was getting it to work on IIS with ActivePerl - if you're running Apache/Linux or BSD it won't be nearly as big a chore. I didn't even have to touch the MySQL schemas.

Content management systems (CMS) not CVS...

[aquarian]

What you want is a content management system, or CMS. These do exactly what you're talking about. There are a whole slew of them out there, free and not free. Furthermore, there are some general web services toolkits with good CMS modules. Find one that comes closest to meeting your needs, then modify it to get exactly what you want. Some that I've used are Zope, OpenACS, Redhat CCM, OpenCMS, MMBase, and Vignette.

Of all of these, I like OpenACS the best, mostly because of its developer community. There are a lot of great people involved, and there's a high signal to noise ratio on the developer forums. Even though OpenACS probably has the least of what you're looking for, it might be the easiest to develop. OpenACS runs on top of Postgres or Oracle, and is written in Tcl.

Redhat CCM is basically a Java rewrite of the original OpenACS. Its CMS modules are supposedly more mature. It runs on a Redhat version of Postgres, and I think Oracle too.

Zope is a whole lotta product, and probably has most of what you're looking for. However, I find it kind of murky, difficult to figure out. YMMV.

These three are the most promising in terms of developer community. This is a bigger undertaking than it might seem at the outset. You'll need all the help you can get, and getting involved with these communities will spare you from trying to reinvent the wheel.

Of course, I'd love to have you guys use and extend the OpenACS toolkit, and share your efforts with the community!