pro's + con's of databases

[Andrew-J2000]

I have a large database that needs to be built, now i was considering using postgres and learning that.

But what i need to do is draw up a report of the positive and negative sides of these databases.

mySQL
SQL
oracle
postgres

and any others you can think of thanks

[Flawless_koder]

My Opinions:

Go for an open source database.
The support base for it is normally better due to the nature of the open source environment.

I use PostgreSQL religiously.
I like the way it works, it's handy dump proceedures - and
most of all - it's lack of native graphical interface. (I'm fairly old fashioned in that respect)

Most tests you'll find will suggest that Postgres is generally faster - but don't take my word for it, or anyones... search Yahoo for Postgres vs. whatever ...

I also like the way stored proceedures work in it, although
the pain of not being able to edit them is annoying - unless
you use external c files - which does increase performance.

Flawless

[JEmLAC]

One I recently got (re-)turned on to was Firebird. (I know I mentioned it in another thread.) I used Interbase a bit back in version 4. Seemed pretty friendly to set up and use, small footprint and all that. Firebird is the open source version of Interbase. The link is here.

Open-source, multi-platform, decent performance (I hear), support for stored procedures and triggers, relatively lightweight. Worth a look!

B

[Pweb]

I'd use MySQL.
I'm using it everywhere, the largest MySQL database I've ever dealt with is over 2,500.000 records, and it's super fast

[Andrew-J2000]

well, I did said it was large, and i'm not sure of the scale that they are requiring until I go there friday. Now it maybe more than 2 million records, which wouldn't surprise me, as I expect that they require a heavy duty database.

[Flawless_koder]

Yeah - i know Postgres can handle a lot more than that @
Andrew - and i know SQL server can quite capably.

Flawless

[Andrew-J2000]

thx 4 the help everyone, but how many can they each hold roughly. I still think postgres is the way to go, as flawless keeps pushing me into

[Flawless_koder]

Lol - others like Marco would push you strongly towards
SQL server i think.

Flawless

[Andrew-J2000]

well, what are the benfits between the two, as ones free i fink and the other costs 1800 init?

[Flawless_koder]

SQL server has easily updated stored proceedures.

That's the main advantage i can give you for it.

Postgres i think is faster... and i prefer it's methods.


Flawless

[MattR]

MS SQL also has a lot of other things:
clustered indexes
dedicated support
full-text search built-in (the postgres one is not stable I think)
replication
text/blob data stored off-row
cluster support

[MarcusJT]

Lol - others like Marco would push you strongly towards SQL server i think.

Actually, since I am not a DBA and don't have enough experience with huge databases, I wasn't going to recommend a database (or comment) at all. However, since I've been implicated, I may as well drop a relevant link into the mix!

http://webmail.postgresql.org/~petere/comparison.html

[Andrew-J2000]

thx for the advice, I had a meeting and they decided to go with access on the front end and sql server backing that on two internal servers on there private networks. Now there will be 130 different networks connecting to the database and at one time there should be no more than 10 clients each person can handle, and they wont all be connecting at the same time so the concurrent no. of users shouldn't be too great.

But the main concerns i have is security as the records are extremely private. Now there were a few things i have looked into, ie ssl etc, but i'm not sure how that would work on a 130 different networks in total accross the uk.

[MarcusJT]

(Now that Andrew has made his decision, this isn't directly relevant to him, but it's probably worth including for the benefit of others who need to make DB backend choices....!)

There are clearly an overwhelming number of factors which need to be thought about when choosing a database, many of which are dependent on the specific needs of the application.

These include number of tables, number of records per table, estimated size of each record, the likely number of concurrent connections which the site's estimated hit rate will require, backup facility requirements, triggers, stored procedures, table/record/field level locking, and any number of other features (these are but the tip of a metaphorical iceberg)!

You must also consider things like what language the app is written in (ASP/PHP/Perl/VB/C#/etc.) since this will have an effect on the performance of the database drivers, the ease with which you can make a DB connection, the time it takes to learn the DB's nuances, maintenance costs, etc.

Once you have considered every conceivable aspect of your site's requirements, you then need to see how each of the different DBs compares. Clearly this is a mammoth task, and the relevant statistics are not likely to be readily available.

Even if they were, I would have thought that by the end of this laborious process, you would be left with results which show each DB to have different strengths and weaknesses, but ultimately all will do the job (I'm assuming that you're not including DB formats & systems like CSV and Access databases in the comparison here).

The only real way to find out which would "best" would be to actually code the entire app for each database system, and then run real-world (and lab environment) stress-tests on the system, measuring the performance of the system in whatever way is appropriate for your project. Only then can you know which is the "best" for the particular project.

Clearly to attempt such a thing would be sheer lunacy, and so unless the app that you are developing is ultra-hyper-speed-critical (which I suspect it's not), pretty much any decent RDBMS will do the job nicely. It really comes down to how much time you have to devote to learning a new DB, how easy it is to integrate with your existing setup, and how much it costs.

That's what I think anyway.

[Flawless_koder]

WOW - i never imagined you a postgres man Marco - I'm impressed!

Perl has a very strong link to Postgres ( and hencely Mason ) - with some it's drivers being exceptional.

If worst comes to worse - across the net you can use
DBD::Proxy if push comes to punch.

Flawless

[MarcusJT]

Originally posted by Flawless_koder
WOW - i never imagined you a postgres man Marco - I'm impressed!

I'm not! I just came across that article and thought it would be relevant - I was not suggesting that PostGres was better or worse than any other database, since I'm not in a position to make such a statement.

I'm surprised that you can actually be impressed by someone's choice of database.... surely choosing the right database for the right purpose (having conducted appropriate research) would be more "impressive" than merely agreeing with your opinion!!

[Flawless_koder]

Not really - i would have been impressed in your choice.

It would have meant that you'd considered the facts and made an intelligent and well based decision on which
rdbms is better. j/king

I just have a lot of faith in Postgres for a lot of reasons,
but you don't find so many of us.

Flawless

[Andrew-J2000]

well, even tho i'm tempted to learn postgres inside & out from flawless, our choice of sql server seems to fit appropriately with what were doing, considering we will be using ms access as a frontend due to the timescale i have to develop it.

but i do have something i would like to find out tho. I received a small questionaire with answers and one of them was this

"will the system be required to be:-

a) networked within each location and/or
b) across locations
c) stand-alone

they said it would be stand alone with one laplink at each location"

does this make any sense to anyone?

[dethfire]

what does everyone think of the new database "Cache"?

[MattR]

Here is what C.J. Date has to say about it (and 'post-relational' gobbldygook):
http://www.pgro.uk7.net/cjd1a.htm

[Spiff Dog]

Originally posted by Andrew-J2000
well, even tho i'm tempted to learn postgres inside & out from flawless, our choice of sql server seems to fit appropriately with what were doing, considering we will be using ms access as a frontend due to the timescale i have to develop it.

but i do have something i would like to find out tho. I received a small questionaire with answers and one of them was this

"will the system be required to be:-

a) networked within each location and/or
b) across locations
c) stand-alone

they said it would be stand alone with one laplink at each location"

does this make any sense to anyone?

I'm not quite certain what they are saying, however, I do know that SQL Server has built in XML capabilities that you can run through HTTP. Technically, you could use this capability for data updates via an Internet link. Since it uses HTTP as it's carrier, it can seamlessly bypass any firewall issues (as long as port 80 is open) and nearly every computer on the face of the earth would then be able to access it. Just be sure to code your application with security in mind.

If you are interested in how to set this capability up, I can post a quick HOWTO on the board here. You can also reference MSDN.

Thanks!
Spiff

[Andrew-J2000]

well i do know remote scripting and the xml method, but the actual system that they appear to have asked for conflicts with that answer. And even the director has said it must be a mistake as it makes no logical sense at all.

Anyway I ended up writing a 10 page report on the advantages of sql server and the different network setups i intended for them, along with any advantages of it. So basically i left them the choice as i can easily manage any solution they wanted