Although open source databases are picking up steam in the commercial market, breaking into the enterprise market and competing with the likes of Oracle and IBM’s DB2 will be tough. “This is rocket science,” Aberdeen’s Bill Claybrook said. In the meantime, the SQL Server 2000 Service Pack 3 (SP3) addresses specific issues discovered in SQL Server 2000 since its ship date.
Battle of the Open Source Databases
About The Author
Eugenia Loli
Ex-programmer, ex-editor in chief at OSNews.com, now a visual artist/filmmaker.
Follow me on Twitter @EugeniaLoli
Great! Don’t smother me with those heavy weight databases,
just give me an Alpha4/FileMaker/Access clone that’s easy to use. Heck, I’d even settle for MaxBase if somebody
resurected it. Some people just need a simple database they can index, query, and generate reports from without
having tobuy a 500 page manual!
Berkeley DB is the best. Seriously, how can you not love it?
I’m in the process of getting familiar with PostgreSQL. It’s really pretty impressive that such a featureful DB is offered free of charge. I’ve developed a few OLTP applications over the years, and everyone I’ve worked for seems to insist on SQL Server or Oracle for the back end. The thing is, they’re paying lots of money for a product they think they need, while a free alternative exists with most of the same features and all of the necessary ones. I wouldn’t use Postgres for a data warehouse just yet, but it certainly seems ready to do the job for OLTP work.
Open-source, multi-platform, InterBase-derived Firebird doesn’t get the recognition it deserves. Check it out: http://www.firebirdsql.org
SAP’s database is opensource, I believe. I haven’t tried it, but it looks like it has a good feature-set. Anybody have any experience with it?
Ask AOL maybe they make a DB that your grandma can run. Klick and Dr00L….
Why is it that so many “IT reporters” or whatever we call the computer trade journalists just don’t get what database systems are really about?
According to the article, the only difference between MySQL and PostgreSQL is that PostgreSQL has had transactions a little longer. This is almost irrelevant to the real issues.
The point is not whether one is “better” than the other. The point is that we are comparing apples to oranges. There are all kinds of data management methods and they each serve different needs. Some want a low level of logical management and integrity, some want a very high level. The level of control over abstraction you have goes up with each choice toward a *truly* relational system (which doesn’t really exist at the moment). Here is a very rough idea of the range of
|< — bits — bytes — filesystem — DBASE — MySQL — Access — PostgreSQL — Oracle — (truly relational DBMS) –>|
Yes, Access actually has more relational functionality than MySQL. So shoot me. But it’s true.
Notice, I’m not talking about performance here, but about actually _what_the_system_does_. Yes, performance is another way to measure database systems, but if that is the only criteria that matters, then we should just store everything in a B-TREE in RAM.
Here is my FAQ answer to the endless MySQL-vs-PostghreSQL comparisons: http://brainscraps.com/faq/pg_my.html
And here is a link to my small rant about any clueless attempt at comparing MySQL to Oracle — http://www.webmaster-forum.net/viewtopic.php?t=133
Those articles like the Newsfactor one, and countless other fatuous articles about “databases” have all the intellectual weight of a smiley-face. But that’s what it’s all about anyway, isn’t it? Just make smiley-faces about the cool “open-source contenders”, without any hint at explaining why there is something to “contend” with in the first place.
I have used Access in the past to create several small database (anywhere from 50-500 records) and write frontends for these .mdb files in Visual Basic 6 (because I hate building frontends with Access).
This works out great because I can build an installer for the app if I need to and transport it and the .mdb file(s) from computer-to-computer without having to worry about connecting to MySQL or something like that, or having it installed on the computer where I am.
However, the biggest downside to this solution is that it is not cross-platform, so I’m looking for another solution. Was wondering if OSS has something for me that will work the same way ?
And just why the hell NOT? Grandmas who use computers might need a low-end database if they have a use for one. Hell, the old PalmOS versions use databases (not particularly powerful ones, but they are).
There is NOTHING wrong with a simple solution to a simple problem — especially if the solution WORKS.
Go back to your troll hole and sleep under your SQL books.
–JM
Systems like Berkley DB are really only the cores of DB systems. BDB really only offers the most minimal functionality that most folks are looking for in a DBMS today.
Things like Alpha V/Access/Filemaker are great tools for a lot of small database needs. They’re nice in that they provide both front end and back end tools to interact with the database, make it easy to create functional applications, etc.
They work well for single user systems and then tend to struggle as the user count goes up for a variety of reasons.
There are a lot of comments regarding MySQL and it’s lack of relation characteristics. But it works well as an SQL shell over an ISAM database, making it much easier to use than a simple ISAM system (like BDB).
Postgres is getting the mind share, but I think Firebird is at a similar level of functionality as Postgres. Today they might be called “Department” databases.
In the small to medium business market, these DBs are really a good fit. Good on performance, lightweight on resources and installation, and able to take a reasonable load.
They give up some integrated services and perhaps some scalability compared to the Oracles of the world, but a vast majority of businesses just simply don’t need the capabilities of Oracle from simply the RDBMS side of the equation.
What businesses DO like, however, is the “peace of mind” of companies like Oracle and MS standing behind the databases.
But, frankly, in all of the companies that I’ve worked with in the past 15 years, something at Postgres’s level on a modern 1-2 GHz PC with good drives is more than capable of keeping up with the load. Considering what folks were doing with the PCs of yore, the modern systems just simply offer so much more it’s really disgusting.
There can be some better work in the front end tools area, I think (no, really!). For small businesses, modern tools are really horrible IMHO. They make it FAR to difficult today, still, after a gazillion years of development.
I agree completely that different situations merit different solutions. My argument is not against MySQL or DBD, etc… but simply that most people don’t understand what the real basis for comparison IS.
In terms of scaleability and enterprise-level replication, there are definitely reasons to go with Oracle, DB2, Sybase, or even (shudder) SQL Server 2000. But, the open source systems such as FireBird, PostgreSQL, and (I think) SAPDB are coming up close in ability to handle the middle range, and in some cases, even the high range of data management. Also, don’t make the mistake of assuming the following:
open-source == free + no support
For example, PostgreSQL offers full support contracts, and so does MySQL. PostgreSQL also offers a commercial enterprise replication server, if you purchase the higher-level support packages. So, you get the best of both worlds: corporate support, as well as an open source system, so you can verify the system does what you want, or even modify it if you need.
>>There can be some better work in the front end tools area, I think (no, really!). For small businesses, modern tools are really horrible IMHO. They make it FAR to difficult today, still, after a gazillion years of development.
Even there, I think PostgreSQL has a better choice than MySQL (please don’t shoot me, everybody). Just see http://pgadmin.postgresql.org/. It’s a very nice windows interface to manage your PostgreSQL system, light-years ahead of phpMyAdmin or anything like that. With a tool like this, combined with ODBC, managing a PostgreSQL database for Windows clients is only marginally more difficult than SQL Server itself. Combine this with the fact that most Linux distributions come with PostgreSQL out of the box, and it really couldn’t be much easier.
All the open source databases (except for SAPDB) are not targeted to the enterprise market to compete with Oracle/DB2 anyway. MySQL has been (and still is) mainly for web apps, SQLite and BerkeleyDB is for embedding, Interbase/Firebird is mainly about small and easily maintained database for business apps, and so is PostgreSQL. Most of these databases do not have “enterprise capabilities” such as builtin clustering/two-way replication, etc.
And yes, greg, you’re right. Firebird deserves more recognition!
I would like to create my database of DVDs. I have already thought out the tables and relations. Now, I don’t want to fiddle around different user interfaces, drivers and backends. In that respect, Access would be perfect, because all I have to do is fire it up, create the tables, fields, references and save it.
Is there ANY other solution, out there, that would satisfy a user like me? I tried, about 2 years ago, to install MySQL on my win32 laptop, and use that, but I didn’t even manage to fire it up. Or if I did, the ODBC driver didn’t seem to notice it. Or maybe the ODBC driver wasn’t configured correctly. I don’t know and I don’t have time to research.
I am thinking of using Paradox, but have no experience with it. I did use WordPerfect and Quattro Pro, and I liked them both, so I thought Paradox might be the solution for me.
Is there any OSS alternative???
Check out http://www.thekompany.com/products/rekall/
It’s not free, but it’s cheaper than Access, and cross-platform. Bear in mind, though, that this is a front-end, and you will still need a database system back-end for the actual storage.
But really, if you already have Access, and you want a quick way to prototype a database, Access is not a bad choice. Access only becomes a problem when you start trying to extend your database into a more complex system, or to scale it to many users, or to make it do complicated scripting tasks.
Mario’s question brings up another distinction in database systems that many don’t understand. The difference between file-based database systems, and database *servers*. Access, Paradox, etc… are examples of file-based systems, which are generally meant for very few synchronous users, since each user works with a full copy of the database in memory. Most SQL systems tend to be *application servers*, which run as daemons, receiving requests and sending back responses, which means they can handle many more users, because each user is not “working with” the database, but merely sending small requests, and receiving data. So of course database servers are by nature a little more complex to set up and manage, and they must be running in the background in order to receive requests.
ODBC muddies the waters a little because it allows any Windows system (desktop, server, whatever) to be the mediator between a file-based system like Access, and a networked remote user, who is making the ODBC request. Thus, Windows itself becomes the application server (although, not a particularly good one, IMHO…).
I have already implemented my solution last nighth, shortly after commenting here. It’s not that I’m so smart, it’s Access that it makes seem easy. BUT…. Access is utterly broken, I must say. It’s exactly as you described it: it doesn’t work well with complex structures. I have created a database with 7 tables with several relations between them. There is the movie table, the media tables, the container tables (DVD set, multi-title DVD boxes, VHS sets etc.) and I am rather proud of the design, but Access borked some of the relationships very badly, ad I even had a situation, after creating a reference, that the primary key in one of the tables, which magically became a longinteger, still contained Box set titles (obviously, ascii text)!
And the funny thing is, I could still add new box set titles without Access complaining one bit about data type incongruence!
Typical MS: user friendly, as long as you don’t barely use it.
I’ll have to simplify the design somehow, to make it work under Access.
Oh, yeah… I forgot to mention the other problem: Access is buggier than the main display at Insect World ;-). Generally these kinds of problems happen when you try to “design while you work”, making type changes, etc… *after* setting up relationships. Really, you should design your tables completely, first, and only then set up the relationships. Access sells itself as a system that allows ad-hoc design changes, but I realy think there is no system that works flawlessly with that approach.
I have done significantly more complex databases than 7 tables in Access, though. Once you learn the annoyances, you find out how to avoid them. Number 1 annoyance is that Access tends to make assumptions behind your back. That is why I often design my tables on paper first (what can I say, I’m a dinosaur), then I play around with design tables as independent, flat tables, then I enforce the relationships, and then I build the view (called “queries” in Access). THEN, I start building the forms. To go any other way is suicide. Also, this will teach you good discipline in any database design.
By the way, “relations” are not the same thing as “relationships”. Relationships are more formally called Foreign Key constraints. A relation is actually the formal definition of a table, according to the relational data model. This is because a relational “table” really involves more logic than just a spreadsheet-style group of columns and rows. You will have to read about such things as “functional dependencies”, etc… to get a better handle on this, but the basic idea is that the Primary Key is really, really important, and should have meaning relating to the data itself, not just as an afterthought.
If you want a great book to give you the basics on databases, read “Database Design for Mere Mortals”, by Hernandez.
OK, I’ve taken this thread off-topic enough. If you want to discuss database design more, visit http://forums.devshed.com/ and we will help out ;-).
I need to have an entry that lists values. This would be an entry that lists DVD IDs. So, it’s not only 1 ID, it’s a variable number of them.