List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:May 23 2007 5:20pm
Subject:Re: design choice - quite many tables
View as plain text  
On Tue, May 22, 2007 23:29, Przemysław Klein said:
> 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
>> My thoughts:
>> Database development questions? Check the forum!
> 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)
Assuming you are using MYISAM table types, each table requires at least
three files on the disk.  If they are in one database they will all be in
one directory (how fast is your OS at finding the files in its directory
Are they going to be opened at the same time (how many file handles can
you have open at once?)?
If separate databases, how do you intend to connect to them (single
connection specifying database or multiple connections).

My feeling is that you would not gain anything by having separate databases.

Good luck.
William R. Mussatto
Systems Engineer

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