database design for reports?

[sysdesigner]

Hi,

 

We have a table with the following structure...

 

|autogen_key|report_id|report_period|numeric_val|line_number|

 

autogen_key == an autogenerated key -- also used to indicate the order that the numbers were inserted in. (same as the order received)

 

report_id == the unique identifier of the number set (one set of values per report)

 

report_period == the month that the report is for

 

numeric_val == the numeric value for a column and row on a report

 

line_number == the line number(row on the report) that the value is associated with

 

 

EG. If the report associated with an input file contained two rows with 3 columns of data in each, then the table would be loaded as follows:

 

|1|employeereport|200506|0|1|

|2|employeereport|200506|2|1|

|3|employeereport|200506|1|1|

|4|employeereport|200506|0|2|

|5|employeereport|200506|0|2|

|6|employeereport|200506|3|2|

 

That would be used to build a report like this..

 

EMPLOYEE LEAVE REPORT

Leave Type

Employee

Number Vacation Sick Other

000100 0 2 1

000103 0 0 3

 

 

We are doing this so that an unlimited number of rows/columns can be added in the future without database changes occurring. For example if the user adds "maternity leave" to the report and the input file, then the table is already set up to accomodate it.

 

My question is, how is best to explicitly tie the ordinals of the row data to the column headings? If the user wants to change the position of the columns, then we want the data associated with a report in the table to reflect that as well.

 

Thanks,

Shawn