Desing tables

[diepa]

Hi, we are designing a system over NT that has several tables. Our biggest concern is over 2 tables, they are the main tables. Its structure are:

create table FLOWDOC
(
DOCUMENTID NUMBER(10, 0) NOT NULL ,
FORMID NUMBER(10, 0) NOT NULL ,
INUSE NUMBER(10, 0) NOT NULL ,
IMAGEFILE VARCHAR2(256) NOT NULL ,
PRIORITY DATE NOT NULL ,
STATE NUMBER(6, 0) NOT NULL ,
SUBSTATE NUMBER(6, 0) ,
SLAVERIFICATION NUMBER(6, 0) ,
KEYINGCENTERID NUMBER(3, 0) ,
PROCESSINGDATE VARCHAR2(10) ,
AMOUNT NUMBER(20, 3) ,
ACCOUNTNUMBER VARCHAR2(16) ,
TRANSACTIONCODE VARCHAR2(16) ,
ABANUMBER VARCHAR2(16) ,
SERIALNUMBER VARCHAR2(16) ,
EXCEPTIONCODE VARCHAR2(2) ,
CYCLECODE NUMBER(4, 0) ,
IMPORTUID NUMBER(10, 0) ,
VERIFIERUID NUMBER(10, 0) DEFAULT NULL,
EXPORTERUID NUMBER(10, 0) ,
ARCHIVERUID NUMBER(10, 0) ,
PURGEDUID NUMBER(10, 0) ,
IMPORTDATE DATE ,
RECOGNIZEDDATE DATE ,
VERIFIEDDATE DATE ,
EXPORTDATE DATE ,
ARCHIVEDATE DATE ,
PURGEDDATE DATE ,
ERRORID NUMBER(10, 0) ,
LASTMODIFIED DATE NOT NULL ,
VIEWPRIORITY DATE ,
ITEMSEQUENCENUMBER VARCHAR2(9) ,
RECOGNITIONUID NUMBER(10, 0) )

With several Indexes and FK. The PK is DOCUMENTID


The table FLOWFIELD is
create table FLOWFIELDS
(
DOCUMENTID NUMBER(10, 0) NOT NULL ,
FORMID NUMBER(10, 0) NOT NULL ,
VERIFIERUID NUMBER(10, 0) DEFAULT NULL,
PRIORITY DATE NOT NULL ,
STATE NUMBER(6, 0) NOT NULL ,
SUBSTATE NUMBER(6, 0) NOT NULL ,
IMAGEFILE VARCHAR2(256) NOT NULL ,
LEFTX NUMBER(6, 2) ,
UPPERY NUMBER(6, 2) ,
RIGHTX NUMBER(6, 2) ,
LOWERY NUMBER(6, 2) ,
PAGENUMBER NUMBER(3, 0) ,
RECOGNIZEDVALUE VARCHAR2(256) ,
RECOGNIZEDDATE DATE ,
ACTUALVALUE VARCHAR2(256) ,
ACTUALVALUEDATE DATE ,
LASTMODIFIED DATE NOT NULL ,
KEYINGCENTERID NUMBER(3, 0) ,
FIELDCODE NUMBER(5, 0) NOT NULL ,
INUSE NUMBER(10, 0) NOT NULL ,
VIEWPRIORITY DATE ,
EXPORTDATE DATE ,
SLAVERIFICATION NUMBER(6, 0) )

Again with several Indexes and FK. The PK is DOCUMENTID, FIELDCODE


The FLOWDOC table receives 750,000 records per day and the FLOWFIELD table receives 2,250,000 per day.
Both tables are on-line tables, that means that during the inserts (during all day several times) our users (100 users) connect to them and perform updates and queries in several fields. Also during the date we delete all the records that were processed (we can't use truncate). The idea is to "clean" the tables after 24 hours to start over again, but we can't truncate the table because we can have at the same time, records from today and from "yesterday".
I'd like to receive some advice about how to create them (storage parameters for the Table, for the indexes Indexes, type of table to use) and if it is a good idea to rebuild Indexes and how often.

Thanks a lot

Diego