Martijn Tonies wrote:
>> 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
> 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
> 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.
_/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/ _/
Przemek Klein (p.klein@stripped)