[Solved] Which database to learn? [Archive]

View Full Version : [Solved] Which database to learn?

graudeejs

October 31st, 2009, 14:53

MySQL or PostgreSQL?
Or any other...?

Which would be easier and faster to learn?
I need to write program (in C), for managing student data....
I figured, the best (probably easiest) way would be to use some database, but I have zero experience in this field....

Currently I think I should go with PostgreSQL

Any thoughts greatly appreciated.

kpedersen

October 31st, 2009, 15:00

I liked working with MySQL because of the sheer amount of coding support for it. (C++, Java and PHP)

From what I understand PostgreSQL is more scalable though.

Learn both if you can, I am sure that their api's are very similar?

graudeejs

October 31st, 2009, 15:12

If I had unlimited time... the sure I would, unfortunately I have very little time

vermaden

October 31st, 2009, 15:13

PostgreSQL all the way.

john_doe

October 31st, 2009, 22:31

sqlite. Because it's simple and supports embedding.client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

danger@

November 1st, 2009, 14:08

I'd go with PostgreSQL nowadays...

graudeejs

November 1st, 2009, 14:59

ye, thanks I figured it's the best option, because:

While sqlite is good, for my app client/server approach is much better.

No doubt that MySQL is most recognized open source DB, but unfortunately it's future is little unclear....

PostgreSQL use BSD license.....

sjakke

November 2nd, 2009, 02:54

I have no experiences with databases, but I hear Nginx is ok and that it is bsd licensed.

Well just checked wikipedia before I posted. Says license is bsd-like.

You know what... Don't listen to me.

graudeejs

November 2nd, 2009, 07:16

I have no experiences with databases, but I hear Nginx is ok and that it is bsd licensed.

Well just checked wikipedia before I posted. Says license is bsd-like.

You know what... Don't listen to me.

http://en.wikipedia.org/wiki/Nginx

nginx (pronounced as "engine X") is a lightweight, high performance web server/reverse proxy and e-mail (IMAP/POP3) proxy, licensed under a BSD-like license.
It's not a database

sim

November 7th, 2009, 01:41

PostgreSQL, for sure:

Mature, rock solid and comprehensive, with excellent documentation and, in my opinion, far fewer gotchas in its compliance. Even though MySQL at last headlines some of the same features that Postgres has had for years (triggers, stored procs, views etc), when you use both systems in earnest you soon discover that the MySQL implementations of these features are far more limited and simply not in the same league.

With PostgreSQL you'll learn better SQL, more advanced DB skills (if you want to), and pick up less bad habits. Plus the skills you learn will be more transferable to Oracle, DB2 etc should you ever go in that direction.

To me, PostgreSQL is to MySQL as FreeBSD is to Linux, but more so. The PostgreSQL development model reminds me of the FreeBSD approach - conservative by nature (for a database, this A Good Thing), with a focus on quality, stability and doing things right. MySQL and Linux always seem a bit 'Wild West' for my tastes :)

Am I biased? You bet! But only because I've used both systems for several years..... I've been developing on Postgres since 2005 and it has NEVER lost a single byte of my data. Last year I had a MySQL system lose records, corrupt tables and become unavailable 4 times over a two month period. Life's too short for that kind of nonsense.

sim

aragon

November 7th, 2009, 04:54

Altho PostgreSQL is technically a more advanced RDBMS, one has to acknowledge that a large majority of open source web stuff is solely dependent on MySQL. One can't ignore it. :)

Have the PostgreSQL project come up with a decent replication system yet?

mwatkins

November 7th, 2009, 07:00

+1 for Postgres. I'm tempted to answer Aragon's query about replication for Postgres with another question: Has MySQL come up with a decent (reliable) database yet? ;-)

More seriously, why limit your quest for knowledge to SQL only? As hardware has advanced the possibilities for other database types have expanded accordingly. Key-value stores are all the rage in some circles - there is a wide variety to choose from. I would encourage you to look around.

While I do favour Postgresql if your primary goal is to get some SQL experience the advice above to look at sqlite should not be ignored. Rather than learning pg or mysql admin, you can focus on C and SQL with sqlite - the database is just a file, no client server setup to distract you.

sim

November 7th, 2009, 11:25

Altho PostgreSQL is technically a more advanced RDBMS, one has to acknowledge that a large majority of open source web stuff is solely dependent on MySQL. One can't ignore it. :)

This is undoubtedly true, and a frequent source of frustration to me when recommending business-critical web-apps for commercial use. Still, web-apps are just one particular use case for a db. The OP say's he's writing his own app, in which case he's free to select the best tool for the job.

Have the PostgreSQL project come up with a decent replication system yet?

This is a hole in PostgreSQL's core feature set, without a doubt. There are several different architectural solutions to replication which is part of the reason why PG has relied on third-party solutions to date. Having said that, simple replication is scheduled for 8.5 (we're on 8.4 now).

Seeker

November 7th, 2009, 16:41

Yes, but what when it comes to PHP
5.3 has an OO support for MySQL and has newest MySQL native driver for PHP (mysqlnd)(replaces and is superior to libmysql)

So for PHP big sites, one should use MySQL and for small to medium sites SQlite3

PostgreSQL for everything else.(In PHP has only procedural support.)

mwatkins

November 7th, 2009, 18:02

Yes, but what when it comes to PHP
5.3 has an OO support for MySQL

That said, the OP indicated the application is to be written in C.

Petz

November 8th, 2009, 10:07

Another lesser known but still free DB is Firebird ---> http://www.firebirdsql.org/

I havn't use it much recently but in a previous job a few years back we migrated from any extremely old and buggy Interbase DB to Firebird(based on Interbase source code originally but since modified a great deal) and the experience was quite smooth.

If the choice was only between MySQL or PostgreSQL I would prefer PostgreSQL.

gilinko

November 8th, 2009, 11:08

That said, the OP indicated the application is to be written in C.

And thus I would say it's more important to learn to write good and proper SQL syntax more than what database backend to use. Improper or non-optimized SQL queries drain system resources quickly independent of database backend, and reduces the speed of the application.

Seeker

November 8th, 2009, 18:57

Can anyone answer how and on which params one should define which database he needs.
Between High level (PostgreSQL, MySQL) and medium to low level (SQLite3) database.

I am using for web MySQL, but am thinking about SQLite3

aragon

November 8th, 2009, 22:41

Can anyone answer how and on which params one should define which database he needs.
Between High level (PostgreSQL, MySQL) and medium to low level (SQLite3) database.
I wouldn't actually call SQLite a "medium to low level" database engine. In some ways it's better than MySQL.

I am using for web MySQL, but am thinking about SQLite3
If you're thinking about it, you probably would benefit from it, so try it. I find all this MySQL dependency among web apps to be a sledgehammer solution. SQLite is more than capable for a lot of applications, and can be far simpler and less resource heavy.

As for PHP OOP DB stuff, what's wrong with PDO? Or does mysqlnd achieve something similar to Axiom (http://divmod.org/trac/wiki/DivmodAxiom)?

phoenix

November 9th, 2009, 01:14

More seriously, why limit your quest for knowledge to SQL only?

Due to the requirements of his project, perhaps? Afterall, would you really consider SQLite when designing a student information system?

SQLite is great for small, embedded databases. But it's not so great for databases that will store GBs of data, and be accessed by multiple clients simultaneously, with a mix of read and write queries.

For something like that, you want a full-fledged RDBMS.

For other projects, SQLite may be a better fit.

Either way, you (OP) should look at learning SQL first (the theory, the concepts, the standardised syntax), and then learn the SQL dialect of your chosen RDBMS.

Personally, I'd recommend PGSQL over MySQL for learning. PG's dialect of SQL is much closer to the standards than MySQL, and is much closer to the dialects of the other big SQL servers (MS SQL Server, Oracle, Firebird, etc). MySQL's dialect is very different from anything else.

mwatkins

November 9th, 2009, 17:38

Due to the requirements of his project, perhaps? Afterall, would you really consider SQLite when designing a student information system?

More than likely not, but we have no idea what the scope of this "system" is to be. The OPs first post indicated nothing that could steer a choice other than (C | Manage some student data | easy to learn | no experience). A follow up post later added += (client/server may be better).

Perhaps the system in question is a write once read many sort of solution, with one writer and many readers. Perhaps the solution is more of a reporting solution, something that sources its data from another solution where it is housed, nurtured, protected. Maybe it's a one-off prof's tool. Maybe it would benefit by being both on line but also portable, storable on a USB drive or a CD? I can think of many situations where sqlite or other non-RDBMS data management tools would be entirely appropriate.

However if the solution requires a high degree of concurrent write and read access; is the principle tool for interacting with and producing new data (i.e. can't be replaced from some other "mother" system); if the solution potentially needs to interact with other software particularly if that software would not be under the author's control, or a host of other situations I can think of - no, I'd opt for a more traditional SQL based solution over sqlite.

I also do not see the choice of sqlite as excluding the use of another RDBMS for a real production system. I said:

While I do favour Postgresql if your primary goal is to get some SQL experience the advice above to look at sqlite should not be ignored.

By this I mean that sqlite source being installed gives the OP a learning ground. If the system is of any significance, the OP's real learning curve is SQL. If it's a minor solution, then learning library calls and SQL are about the same level of complexity. My thought is that sqlite gives the OP a platform to learn some SQL without having to also take on the task of earning how to install and manage a client server DB. But if that is also part of the OP's job responsibility, then yes, indeed he should just press on with a suitable tool for the production system.

Along the same lines, if "learning SQL" before "designing application and database" is a key requirement, in addition to using whatever command line console the DB of choice provides, I would suggest learning sql via the use of a so-called scripting language (only if the OP already is familiar with something like perl or ruby or python or php) to flesh out how your app code is going to interact with a DB. The OP is likely to find doing some exploration in that environment allows for a much more rapid rate of learning, and prototyping solutions in a page or two of python, for example, can often be done in a quarter of the time of many pages of C, especially when there is much new ground to cover.

You also responded to:

I said: More seriously, why limit your quest for knowledge to SQL only?

I injected the thought of being open to "something other than SQL" because sometimes something other than SQL is completely appropriate. Chances are if someone has next to no SQL experience, they probably haven't been exposed to other forms of data management tools. There is a big world out there beyond MySQL, Progress, Oracle, MSSQL et al, and thanks to today's hardware and networks and application architecture styles many solutions are appropriately backed by data stores other than SQL databases.

For all we know this solution has as a principle requirement the management of highly unstructured data that might be better housed in another type of data repository altogether. Or perhaps the data is extremely hierarchical in nature, a situation which an inexperienced SQL developer will find very challenging indeed.

Unfortunately, as is very often the case in threads like these, the OP's "requirements" are sketchy and lead to no definitive direction. Too often the early comments are more about technology rather than nailing down what is the basic problem that needs solving.

Granted, it's a student system of some sort and we (particularly you with your experience) can probably guess as to some of the likely technical requirements. I just prefer not to limit my guesses until the solution needs are more fully detailed, and I also prefer to consider tools other than the hammer because when all you have is a hammer, everything looks like a nail.

PS to the OP: in case it's been forgotten through my verbosity +1 for Postgres was my initial response.

Seeker

November 9th, 2009, 18:31

...Or does mysqlnd achieve something similar to Axiom (http://divmod.org/trac/wiki/DivmodAxiom)?
mysqlnd (http://www.php.net/manual/en/mysqlnd.overview.php)
What it is not

Although MySQL Native Driver is written as a PHP extension, it is important to note that it does not provide a new API to the PHP programmer. The programmer APIs for MySQL database connectivity are provided by the MySQL extension, mysqli and PDO MYSQL. These extensions can now use the services of MySQL Native Driver to communicate with the MySQL Server. Therefore, you should not think of MySQL Native Driver as an API.

graudeejs

November 9th, 2009, 19:51

graudeejs

November 9th, 2009, 19:59

I just could't express myself :)

TerryP

November 9th, 2009, 21:36

Maybe I'm an idiot, but I would say learn SQL, then learn the MySQL, MSQL, PostgreSQL, and Oracle specific crap. SQLite3 is a good kit for just playing around with the basics of SQL based solutions.

mwatkins

November 9th, 2009, 21:36

I don't know what requirements you have for turning in your assignment but one of the advantages of a single-file SQL solution like sqlite is that you can supply the source + compiled app + a database all together for evaluation and grading - the person doing the marking will be able to trivially run the full suite.

zeiz

November 10th, 2009, 01:43

Guys, I don't pretend to hijack this thread but it's just to the point.
12 years ago I've built a db, yes with MSA, and then I polished it for years till the moment I've left MS. This db helps me to manage 3 companies in 3 different countries + my own money...Now what?
Recently I've tried MySQL using its "workbench". On 7.2 it crashes unpredictably and #mysql returns error that it cannot connect.
OOO's Base uses its own HSQL (why btw?) and also crashes during creating of a primitive form. Glom crashes as crazy, Kexi doesn't appear. What else?
Indeed if learn then learn something that won't disappear, degrade or become proprietary.
I've appreciate a link to good tutorial (of which db?) but at least please continue your discussion:)

Seeker

November 10th, 2009, 04:39

SQLite is great for small, embedded databases. But it's not so great for databases that will store GBs of data, and be accessed by multiple clients simultaneously, with a mix of read and write queries.

For something like that, you want a full-fledged RDBMS.

For other projects, SQLite may be a better fit.

I am more of a number type, so please, can I get an answer like:
If simultaneously, there will more than 1000 parasites, write data and 5000 blood suckers, read data, then it is border to shift from SQlite3 to PGSQL :\

Something like that, a? :e

Personally, I'd recommend PGSQL over MySQL for learning. PG's dialect of SQL is much closer to the standards than MySQL, and is much closer to the dialects of the other big SQL servers (MS SQL Server, Oracle, Firebird, etc). MySQL's dialect is very different from anything else.
So in high level, it is PGSQL that wins. :e

graudeejs

November 10th, 2009, 07:15

Maybe I'm an idiot, but I would say learn SQL, then learn the MySQL, MSQL, PostgreSQL, and Oracle specific crap. SQLite3 is a good kit for just playing around with the basics of SQL based solutions.

I'd be glad to learn all IT technologies (not only SQL etc).... but unfortunately I have very limited time (and life is short as well ;) )

dennylin93

November 10th, 2009, 12:31

I have a nature of making simple things complex (sometimes), but by doing so I learn many new things.

This happens to me as well. Looks like I'm not alone ;).

DutchDaemon

November 13th, 2009, 13:54

NoSQL (http://www.rackspacecloud.com/blog/2009/11/09/nosql-ecosystem/)?
NoSQL databases (http://nosql-databases.org/).

waterstof

November 13th, 2009, 17:02

a bit off topic: is C still appropriate for db programming? (I know TO has to do it for education purposes). I do like C very much, but I hear from lots of people that it's only used for system proramming.

graudeejs

November 13th, 2009, 17:39

And why not? C is great language: simple, fast, straightforward, expendable

If it can do the job done, and do it well, why wouldn't it be appropriate?

waterstof

November 13th, 2009, 18:36

And why not? C is great language: simple, fast, straightforward, expendable

If it can do the job done, and do it well, why wouldn't it be appropriate?

labdien, killasmurf86, I agree with you; but I was convinced to learn java. I find it too big, I don't feel comfortable with it, but nevertheless I use it, because I was told that C is only used for low level programming.
OK, I'll put java aside for a while and get back to procedural programming in C. But I don't want to hijack your thread. For me it will be C + sqlite3.

aragon

November 14th, 2009, 20:48

If you want to program, learn C. If you want to make money, learn Java. :)

expl

November 14th, 2009, 21:21

Programm that I'm writing is for my studies in University, Programming languages class.

It's just tast that I must do to pass ;)
I could go anolg and wirte my own dirty and **ity some sort of primitive DB, but I wanted to do it in better way, so i decided to write it as if it was real application, that would be used by university (it won't be).

I have a nature of making simple things complex (sometimes), but by doing so I learn many new things.

Its really simple, you do not use PostgreSQL, MySQL in small/single threaded applications its not practical and its ugly. SQLite is what you have to use unless your application does not do any complicated data manipulation within the database then you should try to use BerkeleyDB for the sake of performance.

achix

December 24th, 2009, 07:46

Last time i checked (2 days ago) mysql didn't respect FK constraints!
Mysql is a nice toy for the web, but not something a large organization would want to rely upon. Not something that could model complex concepts, and relationships.
PostgreSQL on the other hand, seems to respect data and the semantics of the schema in a serious way. In other words, in PostgreSQL, when you say commit, its a commit, when you insert, it *will* check for referential integrity, when you use NULLs, then the DB will handle them as is dictated by the specs and *NOT* based on what the majority of users wants or thinks.
Not to mention those 30+ lines of a single query, which returns the *correct* result in a fully explainable/deterministic/predictable manner!
IMO, PostgreSQL (not to talk about the commercial versions of it), can be a replacement for Oracle in 90-95% of the occasions, out of the box.

And as other people have said, when you learn postgresql, you learn how to be a real DBA. If you learn a toy like sqlite, then it will be equivalent of knowing lets say ms-word or ms-excel.

fonz

December 24th, 2009, 17:24

Last time i checked (2 days ago) mysql didn't respect FK constraints!
[snip]
PostgreSQL on the other hand, seems to respect data and the semantics of the schema in a serious way.
If I'm not mistaken, SQLite also respects most of these kind of constraints. Besides, although you raise a good point, I don't think this is the OP's biggest concern right now.

And as other people have said, when you learn postgresql, you learn how to be a real DBA. If you learn a toy like sqlite, then it will be equivalent of knowing lets say ms-word or ms-excel.
In my opinion, calling SQLite a toy and comparing it to Word or Excel is a bit of an oversimplification.

Killasmurf has indicated that he doesn't have a lot of time and his first priority is just getting his assignment done. For that I'd recommend using SQLite because it's pretty much hassle-free, leaving him with more time to concentrate on C-SQL interaction and on SQL itself [1]. If then later he has the time (and inclination) to dive deeper into the world of databases, his experience with SQLite is a good starting point for learning a full-blown RDBMS such as PostgreSQL. Also, when that time comes he may have other criteria for choosing between Postgres, MySQL or whatever, such as an already existing system he may have to work with.

Alphons

P.S. @killasmurf: whatever you do, please don't pronounce SQL as "sequel"...:e

Edit: ad [1]: Good (i.e. correct as well as efficient) query writing is an art in its own right, the difficulty of which is not to be underestimated.

darkshadow

December 24th, 2009, 22:08

postgres sql support feture that commercal database support ( like triggers , transaction , stored procedure and function etc ) it will simplify the development for you and it under bsd linces so you should have no proplem using it or embed it in your application
on other side mysql start to support what I said from 5 version unlike postgres sql which support that feture from bigging and what make mysql spreed faster it super compination with php , since mysql is fast database which postgres database start to improve it performance from version 8 which you will not care about since you are develop small midum to small application unlike me develop web site application if I where you I will go with posgresql since it standard complimant tooo , I love you postgresql

Alt

January 14th, 2010, 08:33

On my experience, it seems mysql better than pgsql. I developed one of my services on pgsql and thinked "thats better and professional db". Now i migrated project back to mysql/innodb and removed pgsql server. Why ?
- It does not support different encodings in different db. I eated many !@#$ on this.
- No native replication
- No fulltext search (!)
- Non-comfortable internal commands to look triggers etc
- Not so good support in other software

I disliked mysql cus the do not follow standards etc, but they really have all what developer / admin need. Its not so slow compared to pgsql, so i lose nothing gaining many abilities. Now i use mysql/innodb/utf8 while some other apps is cp1251. Seems its not possible on pgsql...

p.s. Im not an phpmysql programmer and i never use phpmyadmin =) And i totally disagree php. But mysql is really better...

vermaden

January 14th, 2010, 12:55

- No native replication

You can say that MySQL does not have it either ,since the 'thinkg' they provide is silly at most, I tried to set it up, always stopped working just like that and waited to be manually restarted with binary log.

- No fulltext search (!)
So what is that ...
http://www.postgresql.org/docs/8.3/static/textsearch.html

- Not so good support in other software
No what?

Alt

January 14th, 2010, 13:41

Ok since 8.3 it have fulltext search (i dont remember which version i was using).
For mysql replication - i know admins using it and it works.
Last - if you want to setup some php-engine or CMS you can get situaion, when you will be forced to install pgsql AND mysql. imho its terrible. Let 1 server to rule many little projects.

But main reasons why i migrated from pgsql - 1)one encoding to whole server 2)my disk got damaged and pgsql process was freezing whole system. mysql survived this (and its data too).
Pgsql is good server and it going great future, but atm i cant imagine myself installing it on production

sim

January 14th, 2010, 15:31

It does not support different encodings in different db. I eated many !@#$ on this.

Really? CREATE DATABASE syntax (http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html)

and

createdb .... -E <encoding> (http://www.postgresql.org/docs/8.4/interactive/app-createdb.html)

sim

Alt

January 14th, 2010, 16:48

Emm.. Does it matter if initdb was in cp1251?

vermaden

January 14th, 2010, 18:26

Pgsql is good server and it going great future, but atm i cant imagine myself installing it on production
You are generalizing a lot here, same with your "- No ..." states, this starting to look as FUD mate ...

danger@

January 14th, 2010, 20:54

- No native replication

PostgreSQL 8.5alpha3 Now Available
Posted on 2009-12-21

Features added in alpha3 include:

* Hot Standby, allowing read-only connections during recovery, provides a built-in master-slave replication solution.

Alt

January 14th, 2010, 22:43

Sorry maybe i was wrong in some points, but i have bad memories about subj xD And i think its not reason for sending FUD

Pushrod

January 15th, 2010, 03:26

Not to pick sides or anything, but if pgsql just got replication in an alpha version that came out less than a month ago, I'd be reluctant to call it mature enough to use in production.

sim

January 16th, 2010, 17:54

Not to pick sides or anything, but if pgsql just got replication in an alpha version that came out less than a month ago, I'd be reluctant to call it mature enough to use in production.

That's a fair and valid point. The pgsql project has a very good reputation for stability and only releasing when ready (not dissimilar to the FreeBSD approach IMO), and they certainly would not advocate using anything other than an official release (not alpha, beta or RC) in production.

To be fair to Alt too, we don't know how long ago he had his experiences - things do move on. :)

sim