Low-cost database platforms

Icon




Overview

Q. I'm an executive director for a non-profit organization that deals with rehabilitation related to disabilities and/or traumatic injuries. The non-profit has been operating for some time but does not presently make use of a database system to track outcomes or cases in general.

We'd like to evaluate approaches that could be used to put such a system in place; ideally, low-cost approaches. What are some of the issues involved?

A. Interestingly enough, I spoke with people from two different non-profits on the same day who both matched this description to some extent; so, the subject is probably of interest to other groups of this type.

This document discusses one possible approach. I'll go over things at a high level for executives. Then I'll outline some technical points briefly in engineering terms for developers.

To point out mistakes or make suggestions, mail me at:

oldcoder@yahoo.com




Suggested approach

In some cases, a simple MS Access database or MS Excel spreadsheet will do the trick. But I'll assume that the non-profits involved are planning for the long term. If this is the case, the groups in question may wish to consider a different route.

The key element is a database server, or central database machine, that's physically located in the non-profit's main office.

The database server holds a database; i.e., a collection of records. Authorized staff may add new records to the database, modify existing records, submit queries related to records, and perform other operations such as preparing letters for a group of people selected based on specified criteria.

For non-profits of the type described above, each record will typically contain fields, or data items, related to one case (or client). Fields might include client contact information, demographic data, services provided, and progress towards goals (where this can be articulated and measured in a meaningful way).

The database server runs open source software and the database itself is stored in an open format. This simultaneously reduces costs and improves flexibility and scalability. The software may include an intranet server that allows staff to access the database using standard web browsers. For more information, see the next section.




Key points for executives

* Can I simply use ordinary Windows programs to make a database?

Yes. For example, you can use MS Access or MS SQL Server. Depending on the number of records involved, MS Excel may also work (an Excel spreadsheet can serve as a database). If the total number of records isn't large and only basic operations are needed, two of the Microsoft programs mentioned here (Access and Excel) are likely to be good choices.

However, Windows programs are part of a world known as closed formats and/or closed source. Depending on your needs, you may wish to explore alternatives from the open formats and/or open source world, which is also known as FOSS.

In particular, if the goal is to maximize flexibility and scalability while keeping costs to a minimum, I'd suggest setting up a database server that runs a FOSS operating system (such as an appropriate Linux distro) and FOSS database software (such as a FOSS SQL or XML framework).

On a related note, FOSS programs are free (unlike most major Windows programs) and open standards provide various additional advantages. For more information on the subject, click here.

* What approach is safest from a security standpoint?

In general, FOSS operating systems, frameworks, and applications are more secure than Windows and/or Windows programs. This is an important subject, so we'll come back to it in a minute; there's a separate section labeled Security issues further down.

* What are the hardware requirements?

For prototype purposes, the database server can be an ordinary PC. Most typical desktop or laptop systems made after 2008 are likely to work as long as they have the following hardware: an X86-compatible processor (preferably faster than 1GHz), a network card, a hard disk of reasonable size, some USB ports, and at least 2GB of RAM. A multi-core processor is recommended but is not required.

The only other significant requirement that's assumed here is the presence of a LAN; i.e., a local network of the type that many offices (small or large) have these days. The LAN is optional; this issue is discussed in the next point.

* How will staff access the database?

The goal is to provide two options if possible. Staff will always be able to perform operations by executing commands on the server machine. Additionally, if a LAN is available, and if network support is desired, staff may be able to access the database from their own machines using standard web browsers such as Firefox or Internet Explorer.

Note: In theory, things could be set up so that people could perform operations not only from their desks but from outside the main office. In practice, making databases accessible this way involves cost and security issues that need to be addressed with care.

* What operating system will the server use?

I'd suggest using any of several possible Linux distros; possibly Absolute, CentOS, or Slackware.

* What database software will the server use?

Possibly one of several versions of a language known as SQL. A simple implementation of SQL known as SQLite might work initially. If a database grows over time, eventually a more complex but also more powerful version of SQL known as MySQL might be used instead.

Databases based on a technology known as XML are also a possibility.

* What will the server operating system and database software cost?

The software should be 100% free. FOSS licenses involve terms and conditions of various types (as do Windows licenses); however, as a general rule, there are no charges or fees.

The database will have costs related to maintenance. For example, somebody will need to make backups (i.e., copies) on a regular basis to protect the data. They will also need to arrange for hardware repairs when things like computer fans and disks break.

* I've heard about the cloud. Would the cloud take care of maintenance?

Yes, to some extent. But the cloud is a different approach that has its own pros and cons. This article may be expanded over time to cover cloud issues.

* What if I want to change things later and go to another approach?

You'll ask a database administrator to migrate the database to a new platform. If standard formats are used and everything is documented, this should be a straightforward process.

* What if resources are limited and I don't want to dedicate a PC specifically to run a database?

The database server could be used for multiple purposes concurrently. For example, it could probably double as a general-purpose intranet server.

* What are the most important things to think about when planning a database system of the type discussed here?

The most important issues to keep in mind are: Goals (the features that need to be supported). Security (in particular, access to records needs to be controlled). Maintenance (for example, the need for backups). Transparency (all formats, programs, and procedures must be documented).





Security issues

As mentioned previously, FOSS is more secure than Windows. This is far from an absolute rule, but it's certainly the pattern.

The higher degree of security that you'll find in FOSS is due partly to superior design (in the case of some FOSS operating systems) but more generally to the nature of the FOSS development process. Interested parties are invited to read Microsoft's own memos on the the subject. For an overview of the memos in question, click here.

The basic principle involved is that problems in major FOSS components are found and corrected quickly; the Windows development process isn't competitive with the FOSS development process (and the Microsoft memos mentioned above appear to acknowledge this).

It's important to note that this principle applies primarily to major components; i.e., FOSS software that's maintained by a significant number of developers and that's widely used in environments of different types. Random FOSS programs that don't meet these requirements are not necessarily more secure than average Windows shareware.

Additionally, no operating system or program is completely secure. For example, human error is an issue; many experienced administrators have learned this lesson the hard way.

But there's a simple way to guarantee one type of security: if you'd like to be sure that data can't make it onto the Internet accidentally, store it on a machine (or LAN) that isn't connected to the Internet.





Technical summary

This section is for developers.

For this application, I'd probably use an X86 Linux box running a server distro (as opposed to a desktop distro such as Ubuntu or PCLinuxOS). A simple setup using lighttpd as a web server, PHP5 to implement a web interface, and SQLite for the database backend might be sufficient for prototype purposes. The database could migrate to MySQL at a later point if necessary.

Alternatively, I might set up a custom server that included its own web interface code and an internal database abstraction layer. The abstraction layer would support one or more standard database frameworks as backends; for example, in one past project, I targeted the three major FOSS SQL implementations (SQLite, MySQL, and PostgreSQL).

Comments are welcome.




Hosting provided by Zymic.

For acknowledgments related to CSS and other code used, click here.

Linked or embedded works (software, books, articles, etc.) are presented under their own licenses.

Other unique content on this page, excluding screenshots, is distributed under the following license: C.C. Attribution NonCommercial ShareAlike 3.0. In some cases, screenshots are distributed under the same license. In other cases, the associated program's license applies.

image Valid XHTML 1.0