From: Martijn Tonies
Date: May 22 2007 10:38am
Subject: Re: design choice - quite many tables
List-Archive: http://lists.mysql.com/mysql/206921
Message-Id: <015801c79c5d$5fc69590$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit

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