Re: Table Design

From: paquette stephane <stephane_paquette_at_yahoo.com>
Date: Wed, 30 Oct 2002 06:59:13 -0800
Message-ID: <F001.004F75B9.20021030065913@fatcity.com>

Have you done a conceptual data model ?
Do all transactions have the same properties ? Do you have established any performance requirements ? Do you favor insert or read ?

If the 10 transaction type have the same properties I would definitively put them in one table.

If your tests show that there is a performance problem you can use the partitionning option (needs the enterprise edition and $$$) then its transparent to your design.

Splitting in 10 tables have the following drawback :

- what if there is a new transaction type ?
- have fun reporting on more than a transaction type !
- what if a transaction was inserted with the wrong

transaction type ? (insert/delete)
....

• PK_Deepa/VGIL_at_vguard.satyam.net.in a écrit : > Hello
  >
  > We are doing database design for a project.
  > We have 10 distinct transactions types and the total
  > number of records is
  > expected to be around 5,00,000
  > taking all transactions together.We have normalised
  > the tables and decided
  > to store all of them together in a
  > single table identified by the transaction type and
  > other unique fields.
  >
  > We would like to know which option would be the best
  > so that we can
  > retrieve data most efficiently
  > Option-1.
  > Maintain 10 different tables for each
  > transaction type (i.e 500000
  > records will be split among 10 tables)
  > Option-2
  > Store all of them together in a single table
  > identified by the
  > transaction type and other unique fields.
  >
  > Regards,
  > Deepa
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author:
  > INET: PK_Deepa/VGIL_at_vguard.satyam.net.in
  >
  > Fat City Network Services -- 858-538-5051
  > http://www.fatcity.com
  > San Diego, California -- Mailing list and web
  > hosting services
  >
  

  ---

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

  ---

Stéphane Paquette
DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant stephane_paquette_at_yahoo.com

---

Lèche-vitrine ou lèche-écran ?
magasinage.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: stephane_paquette_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Wed Oct 30 2002 - 08:59:13 CST