Hi All,
ColdFusion 8 Standard Edition on OS X Leopard.
Developing a new website.
What is the best database to use in conjunction with ColdFusion?
Will be storing simple Credit Card operations, registration information, CRM info for an indie software company
Simple answer: Neither Access nor CF's built-in solution. Either is fine for development, but not for real, live systems.
Better answer is: Whatever will run on your database server. It's a good idea to keep your web server and DB server separate.
Is the live site really going to run on Leopard? Then the answer is going to be whatever runs under Leopard that CF can use the drivers for. Is MySQL an option?
I would look into MySQL and/or PostgreSQL.
I'm more familiar with MySQL. I rather like it a lot. It is solid, with good administration tools. I've used it on OSX servers (years ago) with PHP, and it delivered performance that was more than adequate for our small operation. Definitely worth looking in to.
The answer depends on a number of factors:
How much load are you expecting? If this is an intranet solution to be used by 4 or 5 people on an infrequent basis, the built in SQLLite may be sufficient. If this is a public facing web application which is expected to receive hundreds of orders every day, you'll probably want to run a MySQL or PostgreSQL server alongside it on the same server. If you're looking at hundreds of thousands of orders, you'll most likely want MySQL or PostgreSQL running on a separate server.
If you've got coders with development expertise on Oracle, Sybase, etc.. it might be worthwhile investing in those. If you've got all coders who are just starting out, or who have experience with MySQL, or PostgreSQL, then obviously that makes more sense.
Also consider if there are any other systems which the application interfaces with. If you're going to be working with an order fulfillment system which requires Oracle, then you'll probably want Oracle.
Both PostgreSQL and MySql are good options, especially if you can use those in your production environment. That being said, Derby (the full version, not the embedded version that ships with cf) is being targeted at the enterprise. I'd really like to hear about someone that has pushed some of the limits of Derby with or without CF. The whole written in java thing is very appealing, but seems like it might have issues with performance because it isn't written in a lower level language.
I'd suggest MySQL since its already installed on OS X.
Its also widely supported by hosting companies so you should have no problem finding a good host (including CrystalTech whose logo I see on the bottom right of stackoverflow)
If you go with MySQL, check out MAMP for your development environment. It will install Apache, MySQL and PHP on your machine. Then you can easily add Coldfusion on top if it. I did that on my Macbook and it works like a charm. I would guess that you could link in PostgreSQL to work as well.
MAMP Website: http://www.mamp.info/
For an EXTREMELY helpful video on getting CF8 working on WAMP, check out the following link: http://flashalisious.com/2007/12/14/installing-coldfusion8-on-leopard-using-mamp/
(if you use windows, click the following link: http://www.wampserver.com/
FYI, I have never tried installing Coldfusion on WAMP, but I am sure it will work)