SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    pro's + con's of databases

    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

  2. #2
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  3. #3
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Morning person by habit, not by nature.

  4. #4
    SitePoint Addict
    Join Date
    Apr 2001
    Location
    None
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah - i know Postgres can handle a lot more than that @
    Andrew - and i know SQL server can quite capably.

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  7. #7
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol - others like Marco would push you strongly towards
    SQL server i think.

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  9. #9
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, what are the benfits between the two, as ones free i fink and the other costs 1800 init?

  10. #10
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by M@rco; Jul 21, 2002 at 19:08.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  13. #13
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    (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.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  15. #15
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  16. #16
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  17. #17
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  18. #18
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  19. #19
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what does everyone think of the new database "Cache"?
    Free Science Homework Help
    http://www.physicsforums.com

  20. #20
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is what C.J. Date has to say about it (and 'post-relational' gobbldygook):
    http://www.pgro.uk7.net/cjd1a.htm

  21. #21
    SitePoint Member Spiff Dog's Avatar
    Join Date
    Jun 2002
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Home is where you hang your @."
    Spiff Dog Design

  22. #22
    Currently Occupied; Till Sunda Andrew-J2000's Avatar
    Join Date
    Aug 2001
    Location
    London
    Posts
    2,475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •