General discussion

Locked

database design for changing reports?

By CompSci ·
Hi,

We have a table with the following structure...

|autogen_key|report_id|report_period|numeric_val|l ine_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


HTML Code:
[TABLE]Employee Leave Type
Number Vacation Sick Other
000100 0 2 1
000103 0 0 3
[/TABLE]

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

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

why would

by Jaqui In reply to database design for chang ...

you want to change the database to alter dispay properties?

break the result set into separate arrays for each field.
display the fields in whatever order you want.
if the database design is working fine don't change it.

so:
select all where [condition]
the result set can then be broken into the idividual fields, best for further manipulation of contents, or just display the result set, but name the fields and set in the desired order for display.

Collapse -

Or an add on table

by Tony Hopkinson In reply to why would

Have a column_id against the data and then have another table reportconfig with report_id,user_id,column_id, column_position in it.
Lots of ways to drive that from a UI including stripping columns out and then adding them back in again.
All depends on whether the users are configuring the reports themselves or requesting a newly configured report.
Even better, column_id = Column_position in the original system and all the old stuff keeps working and there's a default state for reports with no config entry.
Got to keep it simple though otherwise you simply re-invent crystal reports.

Collapse -

a newly configured

by Jaqui In reply to Or an add on table

report is a newly confiigured query isn't it?

would you be suggesting a temprary table or permanent table? with the server, doesn't increasing the db size start to affect performance?
if a query is run read only, then the entire result set can be shipped off to the workstation to be manipulated there for layout. ( and running a query to print / produce a report with write access is something I personally would avoid. )

Collapse -

As I said

by Tony Hopkinson In reply to a newly configured

depends on how much 'configuration is required, not going to be that much data though.

One of my favourites was developer intensive to a certain extent, but basically relied on interogating the database for stored procedures spReport.... Well named and typed parameters and a knowledge of how to interrogate them and all you need is a spec for the procedure. Pop it in the db, and thats it.
Better still you can schedule them, then archive to a file and create an entry in table that says it exists which is always nice for out of hours/ reprints for intensive queries.
I often get asked for almost query builder type tools, but I try to avoid that at all costs, you always get some git who runs an aggregate join query on your biggest tables at the busiest time of the day.

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums