I have a background in environmental engineering, but I now have a private counseling practice. I would like to develop a database for my outcome data. I had some experience programing in BASIC and FORTRAN back in the day when everything was on punch cards or paper tape. It’s been awhile.
Where would I start reading to skill myself up for the database development?
I don’t mean to develop a database application. Simply use an existing application to manipulate the database. See what I mean by lack of experience? I don’t even know how to ask the question properly.
I have two sets of data. One would be the clients and their associated characteristics such as age, diagnosis, gender identity and so on. The other set of data is dates and the results of an outcome scale and a client feedback scale for each date.
Assuming I know nothing (and that’s not too far from where I’m at) where do I start to educate myself?
There are various front-ends to the above databases, like mysql-administrator, pgaccess, phpMyAdmin, phpPgAdmin, Webmin, etc
however, if you want to automate the input, you’d have to do some coding on your own (php/perl/python, whatever) or google a bit for automated database input (dunno any so can’t be of much help on those)
Eventually it would be nice to have automated input from the computer administered outcome and feedback measures. However, I think that is something I’ll put off for later. For now, I just want to be able to sort by various client characteristic tags and by outcomes.
Actually, if you know SQL good enough and can code in Bash (or other shell language), then you don’t need any of the above. You can easily write an (interactive) shell script for all of this, even for automated DB input. MySQL and other DBs have command line tools for managing databases/tables
Since Sept 2008 I’ve accumulated only 850 rows. I’ll be adding to that at the rate of about twenty weekly, a little less than 1,000 annually after vacations and holidays are taken out. That seems like a lot to me, but I guess by database standards that’s small potatoes. Sounds like you’re saying it would be more efficient to just stick with the spreadsheet and maybe use macros for repetitive tasks.
Looks like I’m moving this inquiry over to the OpenOffice.org spreadsheet macro forum.
On the Windows side this sort of thing would be typically done in MS Access which makes it easy for a relative newbie to develop a database, front-end and reports. On the Linux side, maybe have a look at OpenOffice Base which appears to fill a similar role. I have no personal experience with it but it looks like it could do what you want you want.
That’s nothing. My XBT torrent tracker, which uses MySQL DB to store stats, has in its stats table 300 thousand rows
Even this is nothing, compared to some other companies DBs. They manage millions if not more of rows
If you would like to learn something about relational DBMS programming but not have to mess with big databases like MySQL or Postgres, you could use a library like sqlite, which has a SQL interface but holds the data all in one file per database.
I would argue that a spreadsheet is a database, just not a relational one.
If you are just dealing with a single table of information, then a spreadsheet should suit you fine.
But if you have sets of data that are related, or you want to do something more sophisticated, like validate the data to ensure that no bad data gets entered - then you really want to use a database.
Also you want to consider how many users will need to access this data. For a beginner, I’d recommend something like Base because you don’t have to concern yourself with installation or configuration issues. You can easily build a form or report based on your data. Base will also allow you to build relationships between sets of data. You just wouldn’t want to use it for a database with hundreds of thousands of rows with several simultaneous users.
I’m not a big fan of using spreadsheets to persist tables of data. It’s very difficult to validate the data before you enter it, so it invites all types of inaccuracies. It’s also difficult to produce reports beyond charts or graphs. Inevitably, you’ll want to query subsets of your data. You could always apply filters to your spreadsheet, but sql will give you much more flexibility and power .
You shouldn’t be afraid to learn to use a database. SQL is pretty easy to learn and conforms somewhat to human language.
For example, this is the sql if you want to select all employees with last name Smith.
SELECT employeeid, firstname, lastname
WHERE lastname = ‘Smith’
It is small & lightweight, doesn’t require you to set up (and therefore secure) network services or other things like that. It has a very simple command-line interface if you want to access your information that way, and most common programming languages can hook into it easily enough. I believe PHP and Python (as of 2.6) ship with it, Mozilla Firefox uses it, Mac OSX ships with it, iPods and iPhones use it… it is a great little lightweight database backend for stuff that doesn’t need to be served up to the rest of the world via the web - though it can probably do that on a limited basis to given the right setup.
and scroll down the menu on the left to ‘Applications: Working with databases’ for a sample of sqlite.
Having spent some time earlier this year with Open Office Base… I think it would be safe to say it would be a very big mistake to equate it to M$ Access. Base is more of a front end, and the default backend database is a Java-based ‘hsqldb’, which being all Java… is a little poky. A number of features in Base are either not implemented or are incomplete/buggy. I thought it rather telling when the forum moderators repeatedly tell people how unsuited Base is, how un-ready it is, how nearly anything else would be a better option for them… after a while of struggling with it, I decided they were probably right. A small, simple database - it works fine. Trying to make a full-fledged database application like you sometimes see with M$ Access… well, by the time you figure out how to implement it in Base (and Writer, and whatever scripting language you use to tie things together), you probably could have done a better job in PHP + MySQL.
I’m a bit late with my two cents but since you’re familiar with the concept of coding and it seems you want to actively participate in your data manipulation (it never hurts to learn MORE), I would suggest using a spreadsheet.
As for data validation, there is nothing to stop you from using forms with your spreadsheet wherein you could validate your data and if you have multiple rules, you could certainly use a separate sheet within the workbook to store the rules, related employee data, lookup tables… the possibilities are endless (I’ve written a spreadsheet based scheduling/payroll system complete with tax rates so I’m sure it can be done).
The one caveat to a spreadsheet based solution is that you need to have a LOT of backups because spreadsheets are fragile and if you break them, don’t expect to ever see your data again… Game Over!
> I’ve used Base and haven’t experienced any problems with it, mindful
> that this isn’t meant to be a production-level database used by several
> concurrent users.
> But for a single user with 700-800 records and a few tables, this
> should work just fine.
> I think it’s a great way for a newbie to ease his/her way into the
> world of databases, mindful that this isn’t meant to replaced MySQL,
> Oracle, or Postgres.
For anyone interested in Databases, I don’t believe there is any benefit
in using Base, Access, etc (toy database environments), over a more real
environment, such as MySQL, which is in the repositories and not
difficult to set up, or Oracle, which can be downloaded for personal use
from the Oracle website.
MS Access actually is not a “toy” database environment. I’ve implemented Access databases with over 100 MB and others with a few hundred thousand rows. If you know what you’re doing, then you can create a nice solution for workgroup (1-25) user applications. You can also use it as a front end (forms, reports) to other ODBC-compliant databases.
And before you start talking about “real” databases, yes, I have used Oracle, Sql Server, Sybase, and Postgresql. Oracle is actually a very over-hyped RDBMS. It’s a memory hog and does not perform as well as SQL Server or Postgresql.
IMHO, based on this person’s original requirements, list of about 800 records, adding 20 new records a week, few tables, etc, I think something like Oracle is overkill. The memory footprint of Oracle for a small project just doesn’t make sense to me.
> MS Access actually is not a “toy” database environment. I’ve
> implemented Access databases with over 100 MB and others with a few
> hundred thousand rows. If you know what you’re doing, then you can
> create a nice solution for workgroup (1-25) user applications. You can
> also use it as a front end (forms, reports) to other ODBC-compliant
> And before you start talking about “real” databases, yes, I have used
> Oracle, Sql Server, Sybase, and Postgresql. Oracle is actually a very
> over-hyped RDBMS. It’s a memory hog and does not perform as well as SQL
> Server or Postgresql.
> IMHO, based on this person’s original requirements, list of about 800
> records, adding 20 new records a week, few tables, etc, I think
> something like Oracle is overkill. The memory footprint of Oracle for a
> small project just doesn’t make sense to me.
I beg to differ, but as MS Access is a single user DB environment, it is
of no use outside of the school classroom.
I simply stated if anyone had a real interest in databases, jumping in
with something scalable wouldn’t be too ambitious.