Re: Table design --ask opinion

Herman,

  Let me give you my thoughts about this :

1. Even the splitting of tables would not help because we will not be able to find out whether it is Outward or outward return from your existing outwardfile schema.
2. The advantages of splitting the tables are :
3. If there is always seperate processing requirements to access "Outward" and "Inward" records. The selectivity condition becomes simple.
4. The data volume in the table will be less than the total volume in a single table approach.
5. The disadvantages of splitting the tables are :
6. If the volume is not balanced between "Outward" and "Inward" and if there is more activity on the lesser volume (say) "Inward", the table could be turn to be a bottleneck if too many process are accessing it.
7. If the requirement is to compare and report both the types then this would mean a simple select turns into a 2 table join stmt.
8. If the tables are to be zapped (in full) after 3 days, if there is no complex and lot of processing requirements on the tables, then the split is not advisable. (This splitting would help us if the table volume is to be retained for a long duration).

     Hope this helps...

Regards
Rajagopal Venkataramany

> There will be about 500.000 records (outward , inward, outward return,
etc)

> Table name : OutwardFile
> ProcessingDateTime
> FileName
> FileFormat
> etc
>
> Table name : InwardFile

On Thu, 20 Jul 2000 19:54:28 -0800, ORACLE-L_at_fatcity.com wrote:

> 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
> I appreciate it v'much
>
> best regards
> Herman Susantio
>
>
>
>
> --
> Author: Herman
> INET: Sherman_at_bcsis.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Regards
Rajagopal Venkataramany Received on Sat Jul 22 2000 - 11:53:21 CDT