Best Database format?

Ok, so i know that everyone will have a different opinion and that none will be right or wrong, but wanted some feedback.

I want to know what everyone thinks is the best database format for my needs?

My needs are:
Has to be completely free
Has to be compatible with the majority of major web hosting sites (crazy domains, go daddy, etc)
Has to be able to be run locally if desired

I am writing an app that will connect to a DB, some users will want the DB to be local (localhost) (not on the net) while other users will want the DB to be available over the internet, most likely in the form of a DB on their website through their own web hosting.

Ideas?

Let the battle begin!

Thanks

To kick it off, i originally thought POSTGRESQL as it is open source and seems to be compatible with most web hosts and has free software available for PC and MAC to run locally.

Im not sure how MySQL stacks up against POSTGRESQL, i believe MySQL is probably more compatible with Web Hosts but not sure if it is completely free in all aspects i wish to bad able to use it in.

These are the only 2 i have ever considered.

Since most web hosts run Wordpress, they have free MySQL as well.

Awesome point, but would that be a reliable enough and have enough speed, throughput to run a business app on?

I should have mentioned that to begin with (my bad)

I am writing (or I should say re-writing) an app for pawnbrokers and secondhand dealers. The system is in heavy use during business hours 7 days a week, with some clients having 10 or more stores. The original program was written in Visual Fox Pro, but seeing as it has now become defunct, I wish to re-write it in xojo (plus the added benefit of Mac compatibility and web app is a huge plus).

Desktop app? If so, write some PHP middleware that actually talks to the DB on the server side. Have your app call the PHP scripts using an HTTP[Secure]Socket. There’s discussion of this approach somewhere in the forums. Search for PHP

For database servers, PostgreSQL is my first recommendation because it is completely free to use and works very well with Xojo. MySQL licensing can get complex resulting in it not being free from cost, although it is certainly up to the task from a technical perspective.

A downside to PostgreSQL that installation and administration can be more difficult.

Sounds great, but I’m afraid its a little beyond my capabilities :frowning:

Only interested in natively supported DB’s from xojo. POSTGRESQL, MySQL seem to be the 2 contenders at the moment. I just want to be able to give the user a screen where they can set the host, username, password and database name and my app connect to it from that.

My budget is small (so self writing majority of the app) and my programming level is low in xojo, in VFP it is quite high, I would say average to high level. But I am older now and learning a new language is not as easy as it used to be :frowning:

SQL is all very new to me, VFP used DBASE IV and I can make programs for that in no time flat, but SQL is quite a different breed. I really want to use SQL for the internet side of things, Local DB’s like DBASE don’t fair well over the internet as you load the whole DB instead of just querying them. So to look up a customer record can take quite some time. Internet side of programming be it SQL or PHP is all new, I am getting my head around SQL, but PHP is just too much at the moment. Also not sure how deployment would work for that. Needs to be a simple install, not uploading PHP scripts to their server for example.

The choice of Your Database always depends on your kind of data, usage and your long term targets. SQLite would be my first choice for small apps with low traffic, simple usage and no need to scale in the future. this could become a bottle neck.

For Business Apps with a lot of traffic, and transactions between more than 10 tables I am always in favor of MySQL. You can start off with typical LAMP server still having the choice to scale up everything on different machines and load balancing servers in case your app will be used by masses of users.

Thanks Paul.

Free DB usage is of huge importance, but I know, depending on the web server, that MySQL can be a part of the package.

Tomas, In the case you state, SQLLite is not an option, but MySQL could be. LAMP servers and load balancing is all above my head :frowning:

In terms of tables, there would be at least 10, more likely 15, but some of those tables would be quite small and not grow. While around 5-8 tables could grow to tens of thousands of records.

An important consideration would be that I would not be administering the Database for the clients, however, I would like to write a function to backup the Database locally from within the app.

Given your criteria, it sounds like you don’t know exactly what your users will need so I’d write it for Postgres, MySQL, and SQLite, and let the customer choose. Some will have easy access to MySQL, others Postgres, while others might just want to run a single-user version (I guess) and not mess with database server setup and admin. You can use mostly generic SQL statements and “if” statements for places where the code has to be database-specific. Yes, it would be a little more work, but you’d reach a broader audience.

This used to be true for decades, but recently Postgres caught up in this area. EnterpriseDB is a company that provides easy-to-install PG packages here. Admittedly, you’ll need to tweak some settings in PostgreSQL.conf, but if you follow instructions given here, you are well on your way.

Or if you are on the Mac have a look at Postgress.app, it is what I use now most of the time for development.

The “community” version of MySQL is free for non-commercial use only. I have read (but cannot confirm) that the MySQL folk have been searching for commercial users of the community version and billing them. I highly recommend MariaDB (https://mariadb.org/) which is a free drop-in replacement for MySQL with no restrictions on commercial use. I believe it was written by the original MySQL developers. I have numerous REALBasic and REALStudio database applications that used MySQL and they all work perfectly with MariaDB.

Just my €2 worth.

I installed Postgres 9.3 a couple of weeks ago on both Windows and Linux computers and it was easy to do. The installers took care of most of the default settings. The only things I had to do was set a couple of config settings to ensure it was listen on port 5432 for remote connections. I also upsized some Visual FoxPro data about 750megs (150 tables) using the “Copy From” feature and it loaded the tables in about 2 minutes and the queries are fast. So I am quit happy using Postgres and would recommend it to others. The pgAdmin tool I find to be quite useful in managing and viewing data.

MySQL is not an option if you want to be able to deliver a local database to your customers, since in that case MySQL is not longer free. MySQL is dual licensed, and the community version license does not allow you to use it for commercial development, only for open source development.

We went with Postgresql for that reason. We use Postgresql online on webservers and locally if needed. We had the same requirements as you.

@Simon White - be very careful with that config. If your server is on the internet, having a direct port to Postgres available may give a hacker a wide open door to your server. Make sure Postresql is kept up to date, or better yet, make some middleware so you’re not accessing the db directly.

Here’s a link to some of the recent vulnerabilities:

http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=Postgre

FWIW, this problem is not limited to PostgreSQL. Any database exposed directly to the internet has these risks.

I have a personal dislike for this project. Monty ‘MySQL’ Widenius sold MySQL to Oracle for 1 billion $ and then complained when they took it in a direction he didn’t like. Then he complained about the dual licensing model he had used himself for MySQL. Now MariaDB again uses more than one license, but at least this time it seems to be GPL for the server (so if you change the server you have to release the source) with some parts BSD licensed, and the client parts seem to be GNU LGPL 2.1 license. You’d have to read that license to see exactly what the requirements are. Overal it is still not as good as Postgresql’s free for every purpose license.

Just my 2 cents.
(and yes it is a personal dislike for this project, because to me it feels lame to receive 1 billion $ and then complain about what the company does with the “no longer yours” project.)

I am developing a database for a client and I went back and forth between Postgres and MySQL. Even though I liked Postgres I went with MySQL because my client may potentially take advantage of other open source projects such as some of the Drupal distros or Moodle or some such open source solution. However, it sounds to me that since I put the database on the server at my client’s facility that it’s not free and they need to pay for it. Could be a big mistake and I may need to either “drop-in” MariaDB or go back to Postgres. Am I understanding that license interpretation correctly?

I would not rely on how anyone here interprets MySQL licensing. Go to the source:

http://www.mysql.com/about/legal/

I’d agree with Paul - get a proper interpretation of the license (ad note that MariDB has many of the same GPL vs not gel vs commercial issues)

That said, PostgreSQL is FREE for any use commercial or not. Period. No ambiguity about its license.
You can buy support from EnterpriseDB but you certainly are not required to.
Besides the fact that I think Postgresql is a superior database the license clarity is a god send.