MySQL and Canned eCommerce
Yesterday I received yet another notice about an ISP that allowed MySQL/PHP/Apache services, but not PostgreSQL. I wanted to find out what I didn't know about MySQL so I started looking into it on barney which has it installed.
Well... I certainly know why they like it. The entire database is file-based. This makes for easy accounting - doesn't it. Since an ISP wants to limit the entire diskspace per client, it makes sense that a database that's file-based is a good thing. Well... the fact is, PostgreSQL is also file-based, but the ownership of the files is that of the process running the database. Is it possible to still calculate the total used space? Yes. But it's more difficult, that's true. So now it's clear why. Let's see what it costs us.
PostgreSQL is very much like any of the high-end commercial packages. Here are a few things that MySQL doesn't have that PostgreSQL does:
- Foreign Keys - this is almost hard to believe. They even say in their documentation that this is a good thing as FKs are difficult to deal with. Now I'm no database snob, but I can't believe that anyone who maintains business critical data thinks this. FKs are the key to referential integrity and without that you have no guarantees that the data you have is related to the key components for it's understanding. Imagine an order item with $1,000 without an order to go with it? Do you assume it's bad data, or do you try and find the customer that's missing a $1,000 item, or find where your receivables are? This is the kind of thinking that I simply can't believe they didn't do.
- Stored Procedures - if you can parse SQL - which they do, then you should be able to parse scripts. If you can do that you can handle stored procedures. There's nothing to it. While it may not be the most efficient language to use, a few additions to straight SQL make it very reasonable for SPs. I'm really amazed that they don't consider this to be a useful addition. But looking at their attitude towards FKs, this doesn't surprise me.
- Transactions - for a database to be SQL-based, I'm amazed that such a fundamental concept can be thoughtfully left out. While I can certainly understand that there are plenty of applications that don't need transactions, there really aren't many that are business critical that don't. This then places the target market for MySQL squarely at the web server, text logs, non-business critical database market. There really isn't any other way to look at all these omissions other than to realize that there is a different market for this database.
- Triggers - this is a little easier to believe because a lot has to go into handling triggers. You ought to have a transaction to have a trigger so you can roll it back if the trigger senses that something's gone wrong.
Combine this with the fact that PostgreSQL is every bit as fast as MySQL and you're in the position of choosing a solid, complete database versus a table/record database. I can't even image why someone you willingly choose MySQL. I can understand why certain shops don't offer PostgreSQL, but that may be as much education as anything else. We'll have to see.
I got word from the ISP for North Hollow Farm that the account was set up for the shopping cart experience. I went and checked it out. I also checked into the UPS tools like address verification, time tracking and package pricing and services. There is good news and bad news about each, I'm afraid.
Most importantly, the shopping cart is reasonably well done with just enough customization to be nice, while still making it wasy for a new person to use. This will be important because there's a lot of data to load/type in, and while I can physically do it, she might as well do it because she knows it, and the maintenance practice will do her good. The bad news is that the order flow is fixed, and there's no way to interject the UPS tools into the flow if they aren't done by the software manufacturer. This isn't really surprising to me, but it's unfortunate.
On the UPS software, even if we could have interrupted the order flow, there's a problem with the UPS tools that I hadn't counted on - XML. The documentation said that the tools were available in HTML and XML forms, but that's only true for the most simplistic of tools. If we needed to address verification or time tracking, then we'd have to have XML, and that means CGI at least, and that's a server configuration issue. Not a pretty sight, I promise you. So... it's probably just as well that the order flow can't be modified - I'm not sure we could have modified it anyway.
This brings me to an idea... I wonder if I should just do it myself and then see how it compares. The issue really isn't anything other than what's best for Leslie. I can't really do card processing, but I can get an interface and try... I haven't done SSL/HTTPS, but I can find out about it and try... I guess it comes down to having the time to try versus doing something else.
I think I'll wait and see what Leslie says. Depending on me for code is not necessarily in her best interests.