Opinions: Dream Database

[Coder3000]

For those in the business I would like to know your opinions. If we could choose such a thing as the ideal database, what would it consist of?

A database to handle huge amounts of data and queries from online, where 1000's of users can be logged on at once. Each user having the ability to add entries to their own unique table within the database, and have every other user the ability to query from it.

How could we streamline the tables to allow for maximum speed?

Looking into having a massive database online with 100's - 1000's of logged in users having the ability to search for criteria.

Any ideas what would be the best setup and databse structure to accomplish this?

[Vaderman]

For those in the business I would like to know your opinions. If we could choose such a thing as the ideal database, what would it consist of?

Oh, this kind of debate has being going on for decades, and probably has created a few wars within Silicon Valley! ;) Besides this is usually the topic for scientists to discuss.


A database to handle huge amounts of data and queries from online, where 1000's of users can be logged on at once. Each user having the ability to add entries to their own unique table within the database, and have every other user the ability to query from it.


I'd personally go for Oracle - It's a well established and reliable database that has a better track record than MS.


How could we streamline the tables to allow for maximum speed?

Streamline?? In database terms, we'd probably call this normalisation.


Looking into having a massive database online with 100's - 1000's of logged in users having the ability to search for criteria.


Ensure that you write SOLID SQL within a properly normalised environment.


Any ideas what would be the best setup and databse structure to accomplish this?

Analyse your customers needs and design your database accordingly into tables, sprocs, views, etc, etc and watch it grow!

Regards

[exterminator]

When the database grows in size .. it becomes quite challenging to keep up to the needs. You may either want to have one big db server to handle all of them or you will need to balance the load onto multiple servers grouped into a cluster.

Oracle 9i has 9 instances of the dbwriters - which actually do the writing of the data into the database pages once various other needed actions are performed. I am not sure about the higher versions.

As for is the number of concurrent users is involved, it also depends a lot on the client side code interfacing the user actions and the db server(s). A right choice of that as well needs to be made. And when you have a thin client user interface, the bottleneck is more prone into the web-server layer than the db layer. There as well, to boost the performance and handle the numerous requests, web-farms are used with a session state server and a router server that allocates the requests onto the different servers within the farm.

Choosing the right database depends a lot upon your specific needs, the platform you are working on, the volumes of data you would be dealing with and the number of concurrent transactions that you might need to handle and the most important one - your budget ;) .

Luckily, I haven't got the chance to decide upon the kind of infrastructure I need to choose for my projects until now :) . However, with the stats that I got for MS SQL Server, I don't see any reasons not to use it.

MS SQL Server 2000 - 709,220 transactions per minute achieved in the published TPC-C test result.
Where TPC-C refers to Transaction Processing Performance council. You may as well want to compare their results with the other database server options that you might be comparing. Here is a nice article you must read - Achieving Massive Scalability with SQL Server (http://www.sql-server-performance.com/dk_massive_scalability.asp). Hope this helps. Regards.

[Shuja Ali]

I would also suggest you to take a look at SQL Server 2005 (http://msdn.microsoft.com/sql/).

Although I haven't used it myself but from what I have heard from others is that it is a very Robust database engine.

[klintan]

Well my dream database is a fast and reliable OODBMS, I would love to be able to load and save my objects seamlessly from and to the database without bothering about how to break aggregate objects into tables or how to implement inheritance in a relational database. I guess such products are already there - only though I have never got my hands on one of them.

[exterminator]

I would also suggest you to take a look at SQL Server 2005 (http://msdn.microsoft.com/sql/).

Although I haven't used it myself but from what I have heard from others is that it is a very Robust database engine.With each release.. they are getting better and better in all terms especially scalability. But some people are quite conservative in using newer versions of the products and like using the stabilized versions. I don't see the reason. Regards.

[Coder3000]

I appreciate your replies. I will give SQL Server a try as I will download the eval to give it a test.

As I design this database I will keep in mind some of your ideas Exterminator in the coding.

Someon had mentioned Cold Fusion from Macromedia as a internet means of accessing the data. Does anyone have any experience with this program, or heard reviews on it?

Thanks