Chris Travers wrote:
It's the learning curve part that I'm finding difficult, not because it's a too complicated, but because I can't find a good source of information to learn from. I'm sure I'll get flamed for this, but I seem to be unable to find information on proper design principle, including where and when to use triggers, stored procs, etc, etc, that isn't 20 years old already. In the liquid world of IT, I find it worrying (perhaps incorrectly) learning from a book written 14 years ago.Tony <[EMAIL PROTECTED]> Wrote:Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL.I started the CRM system I am developing on MySQL before realizing it was the wrong choice. Part of it is simple because people have heard of the software and don't have the time/stamina/patience to do proper research into the benefits of alternatives. There is also a learning curve when going from MySQL to a more standards-compliant RDBMS like PostgreSQL. Heck, I found that going from PostgreSQL to Firebird give me headaches :-P And these RDBMS's have most of the same features!
Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer.I would actually venture to say that many of them are using the RDBMS as a sort of object-persistance store, and not really trying to use the *relational* features of the software. They might as well be using Berkeley DB 4. I know that is how I started with MySQL.
Agreed...
What most of these programmers do not understand is that an RDBMS is not simply a search-engine for stored persistant objects, but is actually a fully-featured information storage management system. With the right features, this information can be stored, queried, presented in another form, etc. all while ensuring that the stored information is EXACTLY what was intended. The tasks that the RDBMS handles include data storage, integrity enforcement, and data presentation. Most MySQL programmers only use it for data storage. Sadly, this is about all MySQL is good for, and hence the barrier to learning how to USE a REAL RDBMS are a bit higher because of the prevalence of the likes of MySQL and MS Access.
Indeed, and I believe that the lack of education with regards to this (or even available information presented at the right level) perpetuates the issue, along with the mis-information put forward by MySQL that these aspects aren't really all that important anyway.
The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily).But again, if you start with the right tools, it is easier to modify later to adapt to changing needs. I think that this is one of the messages we should be presenting. With updateable views, different applications can even have access to different presentations of the data.So on to my point, MySQL guys will happily say "Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this....." and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be convertstoo. Agreed completely. Now we just have to sell the PostgreSQL solution. Here is what the MySQL people will say (and we need good evidence to counter): 1: MySQL is faster. 2: MySQL has more community support. 3: MySQL has replication as part of its core distribution. MySQL's replication is better tested...
1. Let's do apples to apples, NOT apples to Oranges as has been done many times in the past. It would be far more useful IMHO to put forward a "real world" scenario (rather than purely transactions per second) which shows 2 examples of the same application, the first MySQL written with much of the logic in the code, PHP, Perl, whatever. the second would be written in the same language, with the appropriate use of the true RDBMS features in full effect. Then let's comapare results. I'm sure that the more we can usefully abstract to the DB the more performace you'll see from PG, especially as load increases. The big issue I've found is not raw speed, if half of the logic is in regexps and algorithms in the scripting, this will be many times slower to run than the same en DB. Although correct me if I'm way off the mark here. Also the point needs to be made that, it is far more trivial to write other interfaces to the program/application. Who wouldn't enjoy the ability to work on their CRM with a Python or Java frontend, without the need to write 75% of the logic in a different language.
There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say "Hey, look how this can simplify your programming lives" until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one.The basic issue is that many programmers are not taught to value information management systems, such as RDBMS's. These programmers are interested only in the data storage issues of the database, and not on how to use it to manage the information stored therein. Changing this may take a lot of effort. Also, using an RDBMS to its full extent rubs some OO programmers the wrong way because it strikes them as violating rules of OO design. Of course, then why not use an OO database? ;-)
In some respects adding much of the logic to the DB doesn't fly in the face of OO, since you are providing an encapsulated data interface, only needing to change the programatic code in one place
(the DB) for potentially high returns in many places. Hence taking OO one step further than the language and abstracting one level further.
The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers.I agree. But it will take some time to sell, and will require some extremely basic work to get going.I'd gladly help out with such a paper, but find myself in the sad position of my prose being open to attack due to my newbieness in the DB world and not able to speak authoratatively on the subject.I would be happy to help out as well. How about a paper entitled: Why PostgreSQL? It would cover why PostgreSQL is ideal for serious application development.
What about "PostgreSQL - Ideal for any application development"
Note, I have only about 2 years experience with PostgreSQL, and so I would feel more comfortable with lengthy peer review of whatever I write, but I am reasonably familiar with both worlds, and can make a strong case for PostgreSQL, I think.
I have only 2 months and have oodles of enthusiasm, but short on PG knowledge, and even shorter on real RDBMS basics and experience. But I'll be happy to proof read as a novice.