conceptual question regarding partitioning

Does it make sense to logically design the database schema to accomodate partitioning better?

For example:

Table: Account

Table: Run (Has run_id and account_id)

Table: Results (Has run_id and some other columns)

Logically the tables would join: Account > Run > Results with each step of the way down being a one-to-many relationship.

Each account will have a handful of runs, but each run will have hundreds or even thousands of result entries. The Results table is quite huge and growing quite rapidly (50-80 megs per day).

The queries that come in are for a single account. (Show data for "my account" only). I don't want to create duplicate schemas/tables for each account and then use dynamic sql because that would just cause the maintenance to explode.

I want to partition the results table to improve performance.

I've zero experience with table partitioning, but I've been reading up on it. I think HASH partitioning fits our model best.

However, it appears that you need to partition on a column and right now the best "column" to partition on is the account_id (I'm assuming that if can encapsulate all the data for a single account into one partition that queries that come in will be faster since all queries will be constrained to a single account) However account_id doesn't exist in the results table.

I can denormalize my schema and put the account_id into the results table for the sole purpose of table partitioning...

but I'm hesitant since it sort of goes against everything I've been taught over the years...

Just thought I'd put this out there if anyone has any opinions or advice to share...

Thanks,

G

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!