Welcome to WebmasterWorld Guest from 54.205.60.49

Forum Moderators: phranque

Message Too Old, No Replies

Advice on Best database

What is the best database

   
11:58 pm on Sep 5, 2004 (gmt 0)

10+ Year Member



OK I'm new here but think this is the best place to start.
I have a new web site coming on line that will need a database that can take large it counts (500) or more at any given time. This database will contain names that members will search through. There will be as many as 8 to 10 fields for each name containing additional information like nick names, locations, dates etc.
My concern is this database can and will have over 1 to 5 million names in it. And also the amount of people accessing the database at any given time.

So the question is, WHICH database on the market today can handle this kind of throughput without chocking.

Thanks for any advice you can give

Gary

12:13 am on Sep 6, 2004 (gmt 0)

WebmasterWorld Senior Member encyclo is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Welcome to WebmasterWorld [webmasterworld.com], Cpl_Miller.

What platform are you going to be running? Windows or Unix/Linux?

12:43 am on Sep 6, 2004 (gmt 0)

10+ Year Member



5 million isn't that big of a number. So long as you are pulling a single record out based on an index, it will happen in only a few milliseconds.

SQL Server is my favorite database.

12:53 am on Sep 6, 2004 (gmt 0)

10+ Year Member



What about MYSQL server? Is it the same as the SQL your refering to?
I guess I should also tell you I've never built a database before. Are we talking about a long learning curve.
I've worked in the I.T. world for 32 yaers and have never had to work the database side of the fense other than having others do it for my group.
4:50 am on Sep 6, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



MySQL, an open-source project, is different than SQL Server, which is a Microsoft product.

MySQL should handle your needs, I would think. I was a little unclear on exactly how many users you meant simultaneously. Did you mean 500? Is that 500 people on the site, which could translate into 1/x that number of simultaneous database transactions, depending on how often they perform searches?

In any case, MySQL probably can handle it. I have several database tables involving millions of records and there are a few transactions a second without any problem. Properly indexed, they should perform very well. If you were talking about many many gigabytes of data, then I might start getting worried a bit but it sounds like you're talking about a few million relatively simple records (i.e. not full-text searching capability on millions of full-length documents).

As for a learning curve, yes there is one. I personally didn't thing it was very steep at all to learn basic querying and database formation. But you will have to read a book on the subject. What programming language to you plan to use to access the database? My personal feeling is that Perl and PHP are always good but others will do fine as well.

4:57 am on Sep 6, 2004 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



MySQL is a different database than Microsoft SQL Server. Both are quite capable of handling the loads you describe. The biggest difference you will find between the two is going to be price and support, MySQL being the less expensive route.


I've worked in the I.T. world for 32 yaers and have never had to work the database side of the fense other than having others do it for my group.

Now that is impressive ;) That is quite a career in IT without having any db involvement other than management level. I think you'll find MySQL easier to learn and use, with a much broader "open" support structure. Microsoft's solution is a viable solution, no doubt, but I think you'll find it more costly considering the needs you are describing.

1:41 pm on Sep 6, 2004 (gmt 0)

10+ Year Member



The biggest difference you will find between the two is going to be price and support, MySQL being the less expensive route.

A single processor license for Microsoft SQL Server is $5000.

Now, considering how much money businesses have to pay for IT staff, the $5000 is probably LESS than it will cost to mess around with any other database. Using a different database will cost more than $5000 in unnecessary labor.

I've been working with databases for 10 years, and I've used Informix, Oracle, SQL Server, and even some weird databases you probably never heard of. Of all that I've used, SQL Server was by far the easiest to deploy and learn.

You can buy the developer's edition of SQL Server at Amazon.com for ony $42.99.

2:15 pm on Sep 6, 2004 (gmt 0)

10+ Year Member



In answer to some of your questions.

YES, 32 years is a long time to spend on any one career. Most, if not all of my time was in customer field service i.e. desk top support and network services. As most of you know, when working in a large Corporate environment where there are groups of departments for each area it is easy to request a database without knowing how it is actually built.

As for the front end to this database it will be PHP. As to the amount of hits on the database I might explain a little further. This is a military database where military personel are searching for their buddies. A search could be on a name, place (Battle), nick name, or where they attented boot camp. Once this infommation as been located within the database, it then lists all the particulars for than person. Although the email addresses will be in the database, that information will not be shown to the members.
If any of you are former Military you know how important this information is and as such could draw a large number of hits.

With the information you all have provided I'm going to download training information about MYsql and see how fast this old manager can pick it up.

I'd like to thank each of you for your input, it is greatly appreciated.

Gary

3:03 pm on Sep 6, 2004 (gmt 0)

10+ Year Member



I'd recomment you look at PostgreSQL. It is also free as in beer, although they do sell support for much less than a SQL server license.

Since it was only available on Unix until their last version, it has not been as popular as MySQL, however it has much better features and is more standards-compliant.

5:07 pm on Sep 7, 2004 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I think another very important aspect for you is a solid understanding of Structured Query Language (SQL). There are plenty of online resources out there and here is one to get you started...

[sqlcourse.com...]

5:48 pm on Sep 8, 2004 (gmt 0)

10+ Year Member



The purists always love postgress, but miss the point of why mysql is so popular, the old 90/10 thing -- 90% of db apps use 10% of the available features. If you're not building a banking application or something to keep your satellite in orbit, you don't really need transactions.
6:28 pm on Sep 8, 2004 (gmt 0)

10+ Year Member



coopster...

GREAT link...can't thank you enough

All this brings me back to the days of BASIC and Pascal....seems like 25 years ago...hell, maybe it was. ha ha

7:41 pm on Sep 8, 2004 (gmt 0)

10+ Year Member



Oreilly publishes a book "Web Database Applications" that is a good general primer on mysql and the interaction of php with a mysql database.

If your going with php I would definitivly recommend mysql or postgres, I tend to use mysql mainly because I am familiar with it, and because it seems more standard for third party apps, then for any particular (read more legitimate) reason.

Particularly given that there will be time / expenses associated with training either way I think that and that php/mysql examples exist en masse, an open source solution will, I think, prove more cost effective.

8:53 pm on Sep 8, 2004 (gmt 0)

10+ Year Member



SkyDog, are you calling me a purist? :)

There are a number of features that the MySQL team swore they would never put in because no one needed them, and now they have come to their senses and added them. Transactions, foreign key constraints... I believe both are now available with MySQL, but why should I trust a db dev team that so obviously doesn't understand my needs as an app developer?

MySQL seems but a toy after working on Oracle, so I guess I'm biased. I can't begin to explain the number of headaches I've had trying to implement non-trivial SQL statements with MySQL versions prior to 4.1 that did not handle subqueries. I've also faced a couple of MySQL gotchas ( [sql-info.de...] ) such as the one for NULL with TIMESTAMP that had me questionning my sanity.

MySQL will likely handle Cpl_Miller's projected load without a problem. But then you're pretty much stuck with MySQL. Going with PostgreSQL will make it easier to migrate to a more standards-compliant db, such as Oracle, MS-SQL, etc... You're also less likely to have devs working on non-trivial features developing ulcers.

11:14 pm on Sep 8, 2004 (gmt 0)

10+ Year Member



Seems I ALMOST started a riot in here....OK people I guess the point is there are a number of solutions. But what I am getting mostly is that MYSQL will probibly do the deal.

Again, thanks all you've been a big help.

Gary

5:23 am on Sep 9, 2004 (gmt 0)

10+ Year Member



Danny wrote "Going with PostgreSQL will make it easier to migrate to a more standards-compliant db, such as Oracle, MS-SQL, etc"

I disagree, there is nothing that MySQL supports that isn't included in MS-SQL or Oracle. I know that your migration theory isn't true for MS-SQL, I've actually worked with that, if you can migrate a piece of cr*p access database to that, I would worry about migrating MySQL to MS-SQL -- if anybody in their right mind would actually do that. Oracle is another crate of bannanas, however. But from what I understand you would just need to "commit" your queries which may even be handled by an API layer or class, and besides there are creative ways to make MySQL scale.

5:50 pm on Sep 9, 2004 (gmt 0)

WebmasterWorld Administrator ergophobe is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



I think that pretty much every DBMS has some non-standard features that won't work in any other DB, some more so than others.

The one that comes to mind for MySQL is the LIMIT clause. Of course, you can achieve similar results with other DBs, but you would need to rewrite the queries.

You can find some feature comparisons that may be useful.

The mysql site has fairly extensive feature matrix vs DB2 [dev.mysql.com]

The postgres people have a page about migrating from MySQL to Postgres [techdocs.postgresql.org] that gives some useful information.

As for MySQL standards compliance and diversions from the ANSI SQL standards, the mysql manual has a subchapter (chapter 1.8) on MySQL Standards Compliance [dev.mysql.com] which I believe is an honest attempt on the part of the MySQL to document where it observes and diverges from the standard.

3:53 pm on Sep 11, 2004 (gmt 0)

10+ Year Member



No one has mention "Access" which is a powerful database. Is there a problem using this software on the web?

Thanks, and let me know what you think.

4:54 pm on Sep 11, 2004 (gmt 0)

10+ Year Member



Access is easy to use, but it would be stretching it to call it a powerful database. Even MS would tell you to use MS-SQL if you are going to have a few concurrent users, not to mention that its performance degrades fast under load (which is fine; it wasn't designed to for that).
5:02 pm on Sep 11, 2004 (gmt 0)

10+ Year Member



ergophobe - PG has a LIMIT clause: [postgresql.org...]
7:54 pm on Sep 11, 2004 (gmt 0)

WebmasterWorld Administrator ergophobe is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



Okay, I mispoke. I didn't mean to say that it was a feature that no other database has. I meant that every db has proprietary features that are not part of the standard.

LIMIT is in both MySQL and Postgres, but not in Sybase, Oracle or MS Sql Server, Interbase etc etc etc because it's not standard SQL

Tom

8:03 pm on Sep 11, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Personally I'd go with MySQL. After building my own database engine and workign with large nad small systems I find MySQL very capable. It lacks the really serious features like transactions, and the lack of nested queries in the older versions hurts some, but for you're application I think it's a perfact match.

And it has some neat features to let it grow in hardware and performance as you may require it, with master/slave arrangements and round robin load balancing.

SN

2:06 pm on Sep 13, 2004 (gmt 0)

10+ Year Member



The more I read about this subject, the more convinced I am that maybe it would be better to enlist someone that truly knows this stuff. The learning curve just seems to be to large with all the data I'm trying to get inputed. I am working my fingers to the bone just entering data into an Excel spread sheet so I have it in some format that can then be imported into MYsql. And this doesn't leave much time to a database learning curve.

My problem is that there are two of us that are paying for the sites and their upkeep out of our own pockets. We will be selling military products to support the new site, but cash flow is limited at this point. This is the reason I thought I might try and do this on my own.
Can anyone tell me with the information I've provided what a simple database engine would cost to produce?

Thanks everyone

Gary

 

Featured Threads

Hot Threads This Week

Hot Threads This Month