Re: Table design --ask opinion

--0-1025202362-964185168=:17688
Content-Type: text/plain; charset=us-ascii

 I would probably keep everything in one table, and depending on how the purge is supposed to work, I would partition it. Keeping it in one table makes it easier to include all file types on one report. Otherwise you end up having to write UNIONS to bring it all together in one result set. Which may not seem to difficult until you start adding more file types, and therefor more tables to the union. Now adding a new file type involves changing code in the database instead of just adding rows to a table.

For handling the purge, how is it supposed to be done? Is the entire table truncated every 3 days? Or do you keep a rolling window of 3 days in the table? I'm guessing it's the latter in which case I would partition the table on ProcessingDateTime and drop the oldest partition every day to delete it's rows. Then create a new partition to hold the next days rows.

  Herman <Sherman_at_bcsis.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).


---------------------------------
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
--0-1025202362-964185168=:17688
Content-Type: text/html; charset=us-ascii



<P> I would probably keep everything in one table, and depending on how the purge is supposed to work, I would partition it. Keeping it in one table makes it easier to include all file types on one report. Otherwise you end up having to write UNIONS to bring it all together in one result set. Which may not seem to difficult until you start adding more file types, and therefor more tables to the union. Now adding a new file type involves changing code in the database instead of just adding rows to a table. </P>

<P>For handling the purge, how is it supposed to be done? Is the entire table truncated every 3 days? Or do you keep a rolling window of 3 days in the table? I'm guessing it's the latter in which case I would partition the table on ProcessingDateTime and drop the oldest partition every day to delete it's rows. Then create a new partition&nbsp;to hold the next days rows.</P>

<P>&nbsp; <B><I>Herman &lt;Sherman_at_bcsis.com&gt;</I></B> wrote: <BR>

<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Hi,<BR><BR>I'm still new at this mailing list. Looks like this list is very cool., a<BR>lot of thing can be learned ;)<BR><BR>btw, I need your opinions/advises out there.<BR><BR>I'm designing database tables right now,<BR>and I have this one table that actually I can separate it into two tables,<BR>Here is the structure :<BR><BR>Table name : FileInfo<BR>ProcessingDateTime<BR>FileType = Outward, Inward,<BR>Outward Return, Inward Return, etc<BR>FileName<BR>FileFormat<BR>etc<BR><BR>I intend to divide the FileInfo table into two tables , so there's no need<BR>to specify the FileType at these two tables.<BR>and no need to create additional indexes for filetpye, which i might think<BR>will help a lot when creating report ,<BR>because it's already segragated into two tables / de-normalize.<BR><BR>Table name : OutwardFile<BR>ProcessingDateTime<BR>FileName<BR>FileFormat<BR>etc<BR><BR>Table name : In!

wardFile<BR>ProcessingDateTime<BR>FileName<BR>FileFormat<BR>etc<BR><BR>The situation is :<BR>There will be about 500.000 records (outward , inward, outward return, etc)<BR>flowing into FileInfo table per day,<BR>and the management want to purge the data after 3 days.<BR><BR>My questions are :<BR>1. what are the benefits to combine those two information into one table ?<BR>and what are the disadvantages ?<BR>2 what are the benefit to separate those two information into two tables ?<BR>and what are the disadvantages ?<BR><BR>in my opinion :<BR>if I combine into one table, it's more efficient, not too many tables<BR>but the table size will grow very fast, and after 3 days will be<BR>truncated,<BR><BR>I want to know if i choose option 1 or 2,<BR>how will this affect the database pefomance. what is the implication ?<BR>will there be a fragmentation ? at what level ? table level or data file<BR>level ?<BR><BR>Is there a way to monitor which table is fragmented or which data file is<!
BR>fragmented ?<BR>Is there a way to create a table in a specfied data file ?<BR><BR>any comments or suggestions ?<BR><BR>sorry to ask u guys a lot of questions ;)<BR><BR>thanks for all answers, suggestions, or comments<BR>I appreciate it v'much<BR><BR>best regards<BR>Herman Susantio<BR><BR><BR><BR><BR>-- <BR>Author: Herman<BR>INET: Sherman_at_bcsis.com<BR><BR>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051<BR>San Diego, California -- Public Internet access / Mailing Lists<BR>--------------------------------------------------------------------<BR>To REMOVE yourself from this mailing list, send an E-Mail message<BR>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<BR>the message BODY, include a line containing: UNSUB ORACLE-L<BR>(or the name of mailing list you want to be removed from). You may<BR>also send the HELP command for other information (like subscribing).</BLOCKQUOTE><p><br><hr size=1><b>Do You Yahoo!?</b><br>
Get Yahoo! Mail - Free email you can access from anywhere!