Table design --ask opinion

Hi,

I'm still new at this mailing list. Looks like this list is very cool., a lot of thing can be learned ;)

btw, I need your opinions/advises out there.

I'm designing database tables right now, and I have this one table that actually I can separate it into two tables, Here is the structure :

Table name : FileInfo

                        ProcessingDateTime
                        FileType                    = Outward, Inward,
Outward Return, Inward Return, etc
                        FileName
                        FileFormat
                        etc

I intend to divide the FileInfo table into two tables , so there's no need to specify the FileType at these two tables. and no need to create additional indexes for filetpye, which i might think will help a lot when creating report ,
because it's already segragated into two tables / de-normalize.

Table name : OutwardFile

                            ProcessingDateTime
                            FileName
                            FileFormat
                            etc

Table name : InwardFile
                            ProcessingDateTime
                            FileName
                            FileFormat
                            etc

The situation is :
There will be about 500.000 records (outward , inward, outward return, etc) flowing into FileInfo table per day,
and the management want to purge the data after 3 days.

My questions are :
1. what are the benefits to combine those two information into one table ?

    and what are the disadvantages ?
2 what are the benefit to separate those two information into two tables ?

    and what are the disadvantages ?

in my opinion :

    if I combine into one table, it's more efficient, not too many tables     but the table size will grow very fast, and after 3 days will be truncated,

    I want to know if i choose option 1 or 2,     how will this affect the database pefomance. what is the implication ?     will there be a fragmentation ? at what level ? table level or data file level ?

Is there a way to monitor which table is fragmented or which data file is fragmented ?
Is there a way to create a table in a specfied data file ?

any comments or suggestions ?

sorry to ask u guys a lot of questions ;)

thanks for all answers, suggestions, or comments Received on Thu Jul 20 2000 - 21:42:50 CDT