Thread

1. DB Design

   Michael Ryan S. Puncia <mpuncia@census.gov.ph> — 2004-05-19T07:37:06Z

   Hi Guys,
   
      
   
         My question is .. which is better design
   
    
   
   1.	Single Table with 50 million records or
   2.	Multiple Table using inheritance to the parents table
   
    
   
    
   
   I will use this only for query purpose ..
   
    
   
   Thanks ..
   
    
   
    
   
   

2. Re: DB Design

   Andrew McMillan <andrew@catalyst.net.nz> — 2004-05-19T11:21:22Z

   On Wed, 2004-05-19 at 15:37 +0800, Michael Ryan S. Puncia wrote:
   > Hi Guys,
   > 
   >    
   > 
   >       My question is .. which is better design
   > 
   >  
   > 
   >      1. Single Table with 50 million records or
   >      2. Multiple Table using inheritance to the parents table
   
   It's not that simple.
   
   Given your e-mail address I assume you want to store Philippines Census
   data in such a table, but does Census data fit well in a single flat
   table structure?  Not from what I have seen here in NZ, but perhaps
   Census is simpler there.
   
   So to know what the best answer to that question is, people here will
   surely need more and better information from you about database schema,
   record size, indexing and query characteristics, and so on.
   
   
   > I will use this only for query purpose ..
   
   Then you may quite possibly want to consider a different database.
   Particularly if it is single-user query purposes.
   
   For example, there are some SQL databases that would load the entire
   database into RAM from static files, and then allow query against this.
   This can obviously give huge performance improvements in situations
   where volatility is not a problem.
   
   Cheers,
   					Andrew.
   
   -------------------------------------------------------------------------
   Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
   WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
   DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267
                          Do not overtax your powers.
   -------------------------------------------------------------------------
   
   
   

3. Re: DB Design

   Mark Kirkwood <markir@paradise.net.nz> — 2004-05-20T09:28:28Z

   The complete answer is probably "it depends", but this does not help 
   much...:-)
   
   I would try out the simple approach first (i.e one 50 million row 
   table), but read up about :
   
   i) partial indexes and maybe
   ii) clustering
   iii) think about presorting the data before loading to place "likely to 
   be accessed" rows "close" together in the table (if possible).
   iv) get to know the analyze, explain, explain analyze commands....
   
   Best wishes
   
   Mark
   
   Michael Ryan S. Puncia wrote:
   
   > Hi Guys,
   >
   >   
   >
   >       My question is .. which is better design
   >
   >  
   >
   >    1. Single Table with 50 million records or
   >    2. Multiple Table using inheritance to the parents table
   >
   >  
   >
   >  
   >
   > I will use this only for query purpose ..
   >
   >  
   >
   > Thanks ..
   >
   >  
   >
   >  
   >