[PLUG] Database question

Carlos Konstanski ckonstanski at pippiandcarlos.com
Thu Jul 2 22:07:02 UTC 2009


On Thu, 2 Jul 2009, Amy Kelly wrote:

> Date: Thu, 2 Jul 2009 14:27:49 -0700
> From: Amy Kelly <engagedtone at gmail.com>
> Reply-To: "General Linux/UNIX discussion and help;	civil and on-topic"
>     <plug at lists.pdxlinux.org>
> To: "General Linux/UNIX discussion and help,	civil and on-topic"
>     <plug at lists.pdxlinux.org>
> Subject: [PLUG] Database question
> 
> Being currently unemployed like most of PDX, I'm volunteering at a
> place called Rosehaven - http://www.rosehaven.org - and besides having
> the grand chance to randomly sit and knit and crochet with some cool
> ladies, there's a project that I'd like to do for them that would be a
> benefit for me and my resume. I've been a support monkey for a long
> time and haven't learned much more than the very basics needed to know
> if something isn't working correctly in a database. I feel like I'm
> not getting as many hits on the resume as I could be and if I knew SQL
> and such better it'd help since most of the support jobs seem to want
> that, and I'd like to go into QA eventually.
>
> Mostly for my own education, I want to set up a database that would
> track names, addresses, contact numbers and email addresses, and hours
> worked monthly and be able to pull it into a report every quarter,
> plus be easier to keep updated than their current paper system. I know
> you can do this all on a spreadsheet and I'll likely draft it that
> way, what I'm curious about is which tools would be good for this as a
> relational database?  I'd say they have between 100-200 active people
> that would be in it with summer kids and practicum students and such
> included. I'm not interested yet in making it into a login-type
> tracking system because I think that would be too much for them to try
> to maintain, but something that the sign-in sheets would be imputed
> into at the end of the day or the week. I don't want to reinvent the
> wheel, but I do want to try to learn how to set this up by myself.
> Thanks.
>
> -- 
> Amy Kelly // engagedtone at gmail.com

Since this is a linux group (and you won't find me on any Microsoft
groups), I am compelled to suggest postgresql as the database engine.
It is the hands-down winner among the free open source options.  It's
getting darn near as powerful as Oracle, at least from a programmer's
standpoint.  I'm not competent to speak of DBA concerns.

A database engine like postgresql is just a database - no user
interface apart from the psql shell.  Some sort of external front end
must be created.  The front end gathers data from users in a human
friendly format, and then uses that data to construct SQL statements,
and connects to the database in order to issue these statements.

The back end: it isn't too tough to get postgresql up and running on a
linux box, and to learn how to write CREATE TABLE, SELECT and INSERT
statements to create tables and read/write data.  Try here for
starters:

   http://www.postgresql.org/docs/8.3/static/tutorial-install.html

I recommend postgresql 8.3 at this time.  The connectby() function is
worth the use of testing packages.  And postgresql is always a bit of
a pain to upgrade, so you might as well use the newest version now so
you won't have to suffer a version bump for the longest possible time.

The front end: with a postgresql back end, my only suggestions for a
front end would involve custom application programming.  If you're
willing to learn to program, then the next choice to make is web
vs. desktop deployment.  If you want a web browser front end, then PHP
is perhaps the easiest place to start.  If you want to build a desktop
application, then any language with GTK bindings would work.  If you
learn Java, you could use the same language for both types of apps.
And it would look better on your resume than PHP.  It's just that Java
is a PITA for beginners.  You can find help here.

To get an off-the-shelf front end, I know of only two products:
Microsoft Access and FileMaker.  Access can house tables on its own,
or the tables can reside in a SQL Server database.  FileMaker has
similar functionality, only it uses MySQL as its optional backend.
It's what Mac users use.  Both these products can be queried remotely,
but there are serious weirdnesses with both.

There are others on this list who have more experience with ready-made
front end products.  I imagine openoffice is a serious contender.  In
any case, postgresql is probably not a choice if you go the canned
route.

Carlos Konstanski



More information about the PLUG mailing list