homepage Welcome to WebmasterWorld Guest from 54.161.228.29
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / WebmasterWorld / Webmaster General
Forum Library, Charter, Moderators: phranque

Webmaster General Forum

    
Advice on Best database
What is the best database
Cpl_Miller




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

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

 

encyclo




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

Welcome to WebmasterWorld [webmasterworld.com], Cpl_Miller.

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

Small Website Guy




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

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.

Cpl_Miller




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

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.

diamondgrl




msg:358707
 4:50 am on Sep 6, 2004 (gmt 0)

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.

coopster




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

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.

Small Website Guy




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

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.

Cpl_Miller




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

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

danieljean




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

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.

coopster




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

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

SkyDog




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

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.

Cpl_Miller




msg:358714
 6:28 pm on Sep 8, 2004 (gmt 0)

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

nalin




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

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.

danieljean




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

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.

Cpl_Miller




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

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

SkyDog




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

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.

ergophobe




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

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.

designerx




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

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.

danieljean




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

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).

danieljean




msg:358722
 5:02 pm on Sep 11, 2004 (gmt 0)

ergophobe - PG has a LIMIT clause: [postgresql.org...]

ergophobe




msg:358723
 7:54 pm on Sep 11, 2004 (gmt 0)

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

killroy




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

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

Cpl_Miller




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / Webmaster General
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved