i disagree -- data is data. data belongs in the database.

However, a table per report -- that would be "not so smart"

Seems to me a single table would be able to support anything. What are they generating the reports in? are they producing HTML? PDF? XLS? what? what is the OUTPUT format of these reports.


If the data is simply "rows and columns", a single table would suffice for all users:


create table report_lines
( report_id int, seq int,
vc_column1 varchar2(4000),
vc_column2 varchar2(4000),
...
vc_column500 varchar2(4000),
num_column1 number,
num_column2 number,
.....
num_column500 number,
ts_column1 timestamp,
ts_column2 timestamp,
....
ts_column500 timestamp,
primary key(report_id,seq) );


And then they just:

insert into report_header (report_id, time_of_report, username, <whatever>)
values ....;

insert into report_lines (report_id,seq, <whatever columns they need>)
select report_id_seq.currval, rownum, <whatever columns they need>
from (THEIR_QUERY_INCLUDING_ORDER_BYS);


and there you go. You would periodically truncate or dbms_redef this table *if needed* but queries of the form

select <columns>
from report_lines
where report_id = :x
and seq between :lo and :hi;

would always return very fast using the primary key to get the 10/20 rows needed.