Which database to learn?

graudeejs

Son of Beastie

Reaction score: 707
Messages: 4,617

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

Son of Beastie

Reaction score: 2,248
Messages: 3,077

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?
 
OP
graudeejs

graudeejs

Son of Beastie

Reaction score: 707
Messages: 4,617

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

john_doe

Guest


sqlite. Because it's simple and supports embedding.
&quot said:
sqlite faq[/url]"]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@

Administrator
Staff member
Administrator
Moderator
Developer

Reaction score: 426
Messages: 1,022

I'd go with PostgreSQL nowadays...
 
OP
graudeejs

graudeejs

Son of Beastie

Reaction score: 707
Messages: 4,617

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

New Member

Reaction score: 1
Messages: 8

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.
 
OP
graudeejs

graudeejs

Son of Beastie

Reaction score: 707
Messages: 4,617

sjakke said:
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

Active Member

Reaction score: 5
Messages: 157

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

Daemon

Reaction score: 282
Messages: 2,029

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

Active Member

Reaction score: 14
Messages: 120

+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

Active Member

Reaction score: 5
Messages: 157

aragon said:
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.


aragon said:
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

Aspiring Daemon

Reaction score: 17
Messages: 865

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

Active Member

Reaction score: 14
Messages: 120

Seeker said:
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

Member

Reaction score: 11
Messages: 44

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

Well-Known Member

Reaction score: 58
Messages: 416

mwatkins said:
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

Aspiring Daemon

Reaction score: 17
Messages: 865

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

Daemon

Reaction score: 282
Messages: 2,029

Seeker said:
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.

Seeker said:
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?
 

phoenix

Administrator
Staff member
Administrator
Moderator

Reaction score: 1,293
Messages: 4,099

mwatkins said:
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

Active Member

Reaction score: 14
Messages: 120

phoenix said:
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:

I said 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

Aspiring Daemon

Reaction score: 17
Messages: 865

aragon said:
...Or does mysqlnd achieve something similar to Axiom?
mysqlnd
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.
 
OP
graudeejs

graudeejs

Son of Beastie

Reaction score: 707
Messages: 4,617

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.
 

TerryP

New Member


Messages: 11

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.
 
Top