What database format to should I use?

rohan2

Hey,

I need some advice as to what database language/interface I should learn for a specific project. I have about 8 years experience with html, and probably 3 years experience with php. I've never however, done anything that has required database interaction.

I'm on linux based hosting, and essentially the type of website being programmed is a "livejournal" weblog type concept that would support multiple users, groups they would be members of, and messaging between users. Its not a small undertaking.

I work best when there is a visual side of things I can reference. I've worked with a few .mdb databases in the past, and liked the fact that I could open them in Microsoft Access and edit things by hand.

Here's my question - what database system (MySQL, .MDB, or whatever else is out there) should I train myself in? I have about two months to get the hang of things before I need to start coding the site.

Thanks in advance for any advice.
Cheers

Drakla

Well if you're on Linux then using access is pretty much out the window. MySQL does have a pretty good GUI freely downloadable from the usual place MySQLCC (control centre).

Webwise I've only really used MySQL, but Sxooter talks about Postgres so passionately I really want to learn that, too. It does offer more things, but in two months you probably won't need to step outside what MySQL does, plus it's oh so available.

If you've never used it before your main concern will probably be learning the SQL and normalisation and not the ins and outs of what database you use.

Let us know how it goes.

Cheers,
D.

Sxooter

Originally posted by Drakla

Webwise I've only really used MySQL, but Sxooter talks about Postgres so passionately I really want to learn that, too. It does offer more things, but in two months you probably won't need to step outside what MySQL does, plus it's oh so available.

Emphasis added...

Sorry, but PostgreSQL is NOT a sitting target. The things MySQL is adding just now are old, well known, stable technology to PostgreSQL, having been a part of it since day one (or two, maybe three)

Why, when the big bang happened it was already 17 years old (Sorry, a Futurama joke, bad Sxooter.)

But seriously, PostgreSQL has been designed to handle high parallel transaction load for quite some time now, and it's quite good at it.

MySQL AB still doesn't seem real interested in fixing things like numeric and integer overflows with NO ERROR at all. Until those kinds of things are fixed, there are certain jobs, you know, the ones involving math and accounting, that you just can't trust it to get right.

Further, it still seems to be oriented towards the workgroup area, not the enterprise areas, and especially not the enterprise area of financial transactions, with the massive locking and integritry constraints issues that brings up.

If you haven't outgrown MySQL, you just aren't working the kind of area that needs a heavier lifter is all. One day, someone will ask you to handle 1000 simo connects at 200 transactions per second for 8 to 10 hours at a time, and the math has to be right. Then you'll know to look at something else. Until then, MySQL is a fine place to learn, but avoid it's idiosyncracies if you can, like how it allows illegal, illogical, unpredictable output SQL statements like:

select *, count(id) from table group by id

There's way more than a month or two of changes to be made to MySQL to make it a database that can reliably be used for certain application spaces, and I think MySQL's response is gonna be that you should use the new SAP DB derived database, which isn't at all compatible right now, in terms of things like connection libs, and SQL syntax.

Drakla

That's why Postgres is next on my stepping stone path, the extra features. I've been using MySQL to build applications, but haven't actually learnt much about databases from it for a long time. I'm an obsessive learner and want to know what nonsense can be pulled with procedures and triggers, but have enjoyed having to manually do things with mysql that other people have taken for granted, like using temp tables to do stuff that you could do with a subquery even in Access. I suppose if you prefer getting the job done than enjoying the job then MySQL might be worth avoiding.

I'm still like a kid chasing butterflies with databases, though (15 months experience), and will code stuff for the sake of it just for mysql just to see the results.... "wow, look I can find out which postcodes are within 30 miles of my house - now let's actually index it and see how much it speeds it up" kind of thing.

Sxooter

Then you'll be pleasantly surprised by all the cool things postgresql can do. The extra features in PostgreSQL can take quite some time to explore, But the payoff in ease of implementing certain things is huge in return.

You can write stored procs in over a dozen languages, including PHP, hot back up a database while it's undergoing a couple thousand transactions and get a guaranteed coherent snap shot of the database with no performance penalty to the rest of the database server other than the simple select load presented by the backup.

If you get a chance grab a good Celko book like SQL for Smarties or such when you start playing with it, and notice how about 99% of all of it works and works well in PostgreSQL.