November 16, 2006 at 10:28 pm
Introduction
I am designing a database for an application, which will have
1. At least 100 tables, which qualifies for all the rules for normalization.
2. Five major processes doing intensive mathematical calculations. These processes will then dump the processed data into separate tables. These tables will be queried throughout the other processes and will have an addition of 50000+ records each month.
And we need to keep 24 months of data at any time for reporting which means each of the tables
will have at least 1.2m rows at any given time. Each table will have a year & a month field to distinguish the data.
Questions
1. What type of architecture/design/technique should I use to obtain maximum performance & better maintainance?
2. Any idea about design practices and patterns one should follow for designing a new database for large online applications.
3. Any good resource on Internet and books regarding the problem.
--Ramesh
November 17, 2006 at 2:46 am
Nice description!!
One thing that comes to mind right away is that I think you should consider doing this with SQL 2005. There have some major improvements that really benefit data warehousing and other large databases. One thing that would really help you would be to do extensive partitioning.
In SQL 2005, you can simply detach a partition from a table and move it elsewhere ... like to an archive database. In SQL 2000/7 (or in 2005 without the partitions), it's a painful process to archive data out of a large table. 1.2 millions rows of data isn't huge, but it's big enough that performing a large delete can lock the database for a significant period of time.
You would need to decide ahead of time what your archive frequency will be so you can partition accordingly. If you will archive once a year, partition based on year. If monthly then by month. And so on.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply