The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Looking for guidance

We have following scenario in our web application( with sql 2005)...
For a particular scenario,we need to create a table,which will be generated daily with name date_table,and record transactions.A cron action will go through these date tables,and do required processing.Then,the date_tables which have been processed,will be archived.What are possible ways to handle this scenario.
Tuesday, August 07, 2007
Why not have one table and just purge based on a value in a column (like the date)?

Why purge at all?  How much data are we talking about?
Cade Roux Send private email
Tuesday, August 07, 2007
You need to be less abstract and more concrete. There are different solutions depending on your specific assignment.

For example, imagine your friend came up to you and said "I need to move some stuff. I want to buy a car."

The first questions I would ask are, "how much stuff do you have, how much space does it take up, how heavy is it, how fragile is it and how far do you have to take it?"

One obvious problem is that Microsoft Windows does not have cron jobs. You would need to figure out how to use Task Scheduler, Windows Messenger, install a third party application, or pick some other way of scheduling batch processing.

And why do batch processing? If the transactions are relatively simple, just do the transactions in real time and keep the date_tables as a log. Better yet, let SQL Server keep logs of the transactions.

If you can be specific about your problem, you'll usually figure out on your own what can be done and what won't work.
Tuesday, August 07, 2007
This is (or appears to be, given your inadequate description) a common pattern in database design:

1) don't try to create and delete tables.

2) have two tables: current_log and archive_log that are identically structured.

3) write all new entries to current_log.

4) periodially copy all entries older than one day from the current_log to the archive_log and then delete those entries from the current_log.

5) optionally, you can have more than one level of archive_log that can be managed either by automatic or manual processes. I've never seen more than two levels of archive_log (recent_archive_log and historical_archive_log) actually used in practice.
Jeffrey Dutky Send private email
Tuesday, August 07, 2007
I'm totally sure I do not undestand your requirements in full, but have you looked at SQL Server 2005 Integration Services (SSIS)?
Marc Jacobi Send private email
Wednesday, August 08, 2007
Some things to consider...

SQL Server does have a rich job scheduler, called SQL Agent.

Depending on what your "processing" work looks like, you might consider queing up your transactions in a Service Broker queue.  From there you have lots of flexibility about where and when to do the processing work, including real time vs. scheduled and inside vs. outside the database server.

Depending on your volume of data and your query needs, you might want to take advantage of Table Partitioning in SQL Server 2005.  This could radically improve the performance of moving data between active tables and "archive" tables through partition "switching".
Chris Brooks Send private email
Wednesday, August 08, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz