Which database to learn?

[mwatkins]

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]

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]

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]

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]

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]

NoSQL?
NoSQL databases.

 

[waterstof]

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]

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]

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]

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

 

[expl]

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]

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]

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]

op

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]

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]

- 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]

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]

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

Really? CREATE DATABASE syntax

and

createdb .... -E <encoding>

sim

 

[Alt]

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

 

[vermaden]

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@]

- 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]

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]

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]

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