What better example for an architecture for this business problem than
Walmart?

As Thomas says, one logical table for transactions and one logical table
for line items.

>From a performance perspective, keep two physical tables for each

logical table: one for "active" transactions - i.e. those less than a
week old, and the second for "warehoused" transactions - those 1 week
old or more (suggested age limit - make it configurable).

On a nightly basis, move all transactions beyond the age limit and their
line items from "active" to "warehoused" tables in a batch job.

Pay attention to Sarbanes-Oxley requirements for your transaction
boundaries. I believe that posting a row at the start of the
transaction, then posting the line items together at the end of the
transaction, with an update to the transaction header to show totals and
completed/canceled/voided state would do the trick. This way, each
transaction (from the users perspective) actually involves two
transaction commit points (from the DBMS perspective), with both commit
points requiring just a few milliseconds.

Walmart takes this one step further and pushes the data, in near real
time, to their corporate headquarters for stock management.

On Wed, 2005-07-20 at 06:23 +0000, women_lover_best wrote:
> We are developing a POS application..on average there will be 50-60
> terminals connecting to server..and there will be many transactions in
> day(guess 1 to 2 lakh)..now should i make my transaction table for
> each month..which means there will be 12 tables..or is there any other
> way..i am thinking from performance and management..and reports..
> thks
>