[plug] Database advice - Newbie

[Craig Ringer]

>
>
>I need to set-up a database which will probably be standalone
>application, but at short notice may need to be developed to web
>database.
>
OK, ideally it won't matter much what DB you use. I believe that both 
MySQL and PostgreSQL run on Windows, though you'll need to look into it 
further. That is, if you're setting up on a Windows platform - you 
didn't mention, but it sounded likely given the reccomended apps.

>Obviously I would prefer to use free/Linux software (my business partner
>is less computer literate than I) and am thinking of postgresql with
>Java  because we may also need to access data from other hardware
>(various types of swipe cards and exercise equipment) - on a Linux
>server.  Cost is also an issue, is it easy/quick for Java programmers
>and is it the best option.
>
I think both PostgreSQL and MySQL have JDBC drivers, so you should be 
alright on the interface to the (java-based) client app. Avoid MySQL 3, 
since it lacks some significant features like transactions and subqueries.

I haven't worked much with Java, so I can't say for sure how well 
maintained these projects are, but they'll provide the JDBC interface 
you'll need:

http://jdbc.postgresql.org/
http://www.mysql.com/downloads/api-jdbc-stable.html

It should be as simple as installing the database onto the host, setting 
up JDBC, and starting work. Certainly it's that easy when working with 
those databases under Linux with languages like Python and PHP - most 
distros have everything packaged in a fully set-up state. I don't know 
how much fuss you'll encounter installing and setting up on Windows, but 
I don't expect it'll be excessive. MySQL has an installer for win32, so 
presumably that won't be much fuss. I don't know the status of a native 
PostgreSQL port for Windows - there is one, or was, but I don't know any 
more.

I'd be inclined to suggest MySQL 4 if you are going to to run the DB 
server under Windows, or pick either if the DB server is to run under a 
*NIX host.

It should be easy enough that you can download the software and just try 
it out with a few sample Java apps that talk to a database.

Craig Ringer