List:General Discussion« Previous MessageNext Message »
From:Przemysław Klein Date:May 23 2007 6:29am
Subject:Re: design choice - quite many tables
View as plain text  
Martijn Tonies wrote:
> Hi,
>
>   
>> I'm working on quite big database. It consists of about 200 tables.
>> Additionaly about 50 tables are per year (because of annual data). It
>> means every year new 50 tables will have to appear in application. And
>> now I have a question. Should I use separate databases for "annual" data
>> (i.e. db2006, db2007, etc...) (i don't need constraints on that (annual)
>> tables) or put all the tables in one database? Is there any way to
>> 'catalogue'/organize tables within one database (namespace/schema)?
>>
>> Any thoughts?
>>     
>
> Yes, in my opinion, you should use the same tables for each year. So no
> "tables per year" or "databases per year", unless there is a very very
> specific
> reason for this.
>
> Having tables on a per-year basis also means you cannot do cross-year
> queries easily and you have to adjust your queries according to the current
> year.
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
>   
The reason of distribute annual data into different tables is that they 
are NOT small. They store business documents in my company and can count 
about 500k rows (and will grow each year). After performance tests we 
did, it occurs that keeping those data in one table (with additional 
column 'year') wouldn't meet our response time requirements.
I realize that this approach is not proper from relational point of 
view, but it seems that we must separate annual data. Now, the question 
is: if we should keep them in one database (and be prepared for database 
with approx 500 tables after 3-4 years) or in multiple databases.

Regards,

-- 
_/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/
Przemek Klein (p.klein@stripped)

Thread
design choice - quite many tablesPrzemysław Klein22 May
  • Re: design choice - quite many tablesBrent Baisley22 May
  • Re: design choice - quite many tablesMartijn Tonies22 May
    • Re: design choice - quite many tablesPrzemysław Klein23 May
      • Re: design choice - quite many tablesWm Mussatto23 May
        • Re: design choice - quite many tablesPrzemys?aw Klein24 May
          • Re: design choice - quite many tablesWm Mussatto24 May