General DB question - looking for best option

[WorkingMom3]

I need some suggestions/advice.
I've been asked to write an application for a new venture on which a friend is about to embark.

We're looking at no more than a 1 year turn around.

In the Microsoft arena, I've done C++ (6.0 and some .net) for the last 8 years, VB and VBA programming...off and on...for about 7 years, as well as experience with many other languages.

This application would need to I/F with a database of hundreds of thousands of clients. We'd need the ability to archive client data when expired (without removing it) as well as generate reports & statistical charts. I've never written an application like this before (using a databse) and trying to determine the best language and database to use...one that places the least amount of restrictions on the customers.

So I guess the question is...what are the options...what could provide the easiest turn around...work best with C++ or VB...how can I find out my options?? I'm a little overwhelmed not understanding my choices.

Thanks in advance.

 

[mrdenny]

The database platform can be just about anything. You can use MySQL, Microsoft SQL Server, Oracle, etc. SQL Server and Oracle both have free versions, but they have limits. To get larger versions of the databases you'll need the paid for version of the databases.

Personally I'm a SQL Server guy myself. It can easily handle the load you are talking about. As for the client application C#.NET or VB.NET will probably be the easiest. C++ will also work, but will require more work.

The other nice thing about using Microsoft SQL Server as the database is that you don't have to install or configure any database components on the client workstations as Windows comes with all the SQL Server drives preinstalled.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[johnwm]

As an alternative if you're familiar with classic VB there is no particular reason to change. It will happily interface via ADO onto any of the standard databases. For modest database needs you can use MSSQL free edition. For heavy duty use I would go with MySQL. It's open source, heavy duty and free. You only need to buy the support you need.

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[WorkingMom3]

Thank you so much! That helps tons and gives me the direction I need.

 

[SQLSister]

Hundred of thousands of clients.. Database performance will be a significant issue. Don't do any designing until you read up on that for the database you have chosen. The database design is a key element in how well it will perform and the hardest thing to change after the go live date when you find out performance isn't good enough.

Database programming and design is a specialty in it's own self. This sounds like a large project. If you can hire another developer to help with this end of things (since you have little database experience from what you said above), it will help the project move faster. If not then one thing you need to start doing is learning to think in terms of sets not in terms of manipulating one record ata time. Also int erms of general database understanding and knowledge herw are some helpful articles. Some are SQL Server specific, but reading thenm will give you an idea of what kinds of things you need to think about in doing the designing and programming

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx.

http://www.tek-tips.com/faqs.cfm?fid=4785

http://www.sommarskog.se/dynamic_sql.html

You will need to learn about indexing as well (sorry dont; havea good article handy onthat one) and a good perfromance Tuning book for your selected database backend is a must as well as a good book on the flavor of SQL it uses.


"NOTHING is more important in a database than integrity." ESquared

 

[harebrain]

Hundred of thousands of clients..

Just goes to show that the original post was ambiguous. SQLSister, I don't think the OP meant that there would be 100,000 client users, rather that this would be a database of 100,000 records of client data.

OK, mom, which is it?

 

[mrdenny]

That's a very good point. The answer to that question will determine a lot about how much horse power your database need will need to be able to handle.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[WorkingMom3]

Yes...hundreds of thousands of clients meant

The business will have hundreds of thousands of records of client data.

I'll take a look at those articles.
I feel like such a newbie! All advice is greatly appreciated!

 

[Crowley16]

hundreds of thousands of client records actually counts as a small scale database.

Just about any RDBMS can handle that sort of numbers with ease.

As others have said before, familiarise yourself with general database principals first. Database platforms will most likely be decided by cost and ease of deployment.

--------------------
Procrastinate Now!

 

[genomon]

For something of this scale, have a look at MS Access. Nice to have built-in frontend forms, reports, and queries all in one place.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[MDXer]

We'd need the ability to archive client data when expired (without removing it) as well as generate reports & statistical charts.

If reporting is to be the primary role of the data base then a star schema may be a better option.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[WorkingMom3]

Those articles bought back what I learned in college but haven't touched since.

And really...hundreds of thousands of data records is considered a small database?

Thank you all.

 

[Crowley16]

yes, hundreds of thousands of records is a small database.

a better measure would be the disk size of a database, since that takes into account structural complexity, i.e. lots of tables.

anything under a gigabyte I'd consider to be a small database...

--------------------
Procrastinate Now!

 

[MDXer]

I consider anything under 500GB as small.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mrdenny]

Database size isn't the only measure of how large a database is. Transactions per second also need to be included. A 10 Gig database with 50k tps would probably be a large database.

A 500 Gig database with 50 tps probably isn't such a large database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[lespaul]

I don't know the size but we have tables that are storing case information going back 25 years with tables that contain millions of records...defendants, charges, hearings, sentences.

Leslie

Have you met Hardy Heron?

 

[WorkingMom3]

I'm sorry...

"A 10 Gig database with 50k tps would probably be a large database."

"a better measure would be the disk size of a database, since that takes into account structural complexity, i.e. lots of tables."

Could someone expand or point me to articles directly related to this? How would you measure tps? Obviously this needs to be a consideration during development - what sort of analysis would you do to help you determine this?


"I don't know the size but we have tables that are storing case information going back 25 years with tables that contain millions of records...defendants, charges, hearings, sentences."

What kind of database are you using?

To All...sincerest appreciation for helping me come up to speed on this.

 

[mrdenny]

The transactions per second is a perfmon counter that you can look at. It's under MSSQLServeratabases > Transactions / sec.

I'm not aware of any specific articles which document any guidelines as there really aren't any. There are a lot of variables that need to be taken into account when deciding if it's a large database. There are no hard and fast rules.

Some of the variables will be:
Space used
Data acquisition rate
data deletion rate
Transactions per second
Uptime requirements

What makes this harder to figure out is one of the other variables is the skill of the DBA managing the database. For some DBAs a large database would be something that is 50 Gigs with 100 Tps that has a 9-5 uptime requrement, for others 1TB with 30k tps with a 24x7 uptime is a large database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mrdenny]

All that being said, I'm in the middle of a couple of performance tuning articles, and this will probably be something which I cover within the articles.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MDXer]

TPS is a valid measure of an OLTP system but if it is an Data Warehouse/OLAP system then table size is a better measure. Dimensions with 100s of thousands and fact tables with 100s of millions to billions to me is a VLDB, but they may have a low TPS.

The original post states

This application would need to I/F with a database of hundreds of thousands of clients. We'd need the ability to archive client data when expired (without removing it) as well as generate reports & statistical charts.

In my world this is a Data Warehouse based in a star schema, as the need is to archive records and report against them. TPS for processing the data in should be less of an issue than the query time to satisfy reporting needs.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck