Topic: DB design

There's no separate DB forum here, so I figure any PHP users are also MySQL users and would be able to pontificate thusly.

I am designing an application right now that will have a 'reporting' class. Right now, the only report I have to generate is a .csv file containing customer contact info to be imported into a mailing label generator.

I anticipate other reports being required at some point.

Saving those reports in the DB so they can be presented in other formats (HTML? XML? PDF?) seems like a smart way to go.

But without knowing what a 'report' will be...I need flexibility.

So, I'm thinking something along the lines of

report
---------
report_id

report_fields
---------
report_id
field_name
field_content

That way, any report can have any number of fields.

The class can just build it all as an associative array and work with it from there.

Seems pretty simple to me...am I missing something obvious that would make a better way to do it?

Re: DB design

maybe I'm missing something. So you'll have your regular data in the DB and then when someone wants a report it copies that data into the report table(s)?

I have never really heard of storing report data in a database. I've always queried the needed data with whatever filter is needed and delivered it via the application.

It seems to me that the better approach is to have your program do the actual reports. Assuming PHP... you would have PHP generate the CSV and create a  file and store that file in a reports folder (if you need to archive that report) or stream it down to the client. That way if you add a field or something to the data that you aren't messing with DB structure everytime... you just change change your Query.

perhaps I misunderstood.

Last edited by tank (2009-01-22 15:42:09)

I dream with an XML intereface

Re: DB design

I have to agree with tank.  Why create the added overhead of "report" tables when they will be a redistribution of data that already resides in existing tables.  Running a query to pull from existing data will be no different than running a query against your new report tables.  Besides, once the existing data changes, your report tables will be stale and out of synch.

That's my 2 cents :^{>

Honored to Serve for Him - Tom ('Mas) Pickering <)><