which is better -- separate tables for each Report's data, or one table for all reports data?

Question

I need to re-engineer an Excel project which produces 4 daily Reports.  In each of these 4 Reports we collect daily (numeric) data and graph it and compare it to data collected from the previous years - for each report.  The original report in this project started 10 years ago, then we added another report and started collecting data for that report, and then a few years later added another report (to the same excel file).  Now we are up to 4 reports.  The Excel file is getting kind of large now, so I want to transfer the data we have collected to sql server tables and generate the reports from the sql server tables. 

Each report has essentially the same format.  I am thinking I could make 4 separate tables with fields like ReportType, Count, SubjectCode, CountDate (to name some of the essentials fields).   Or I could have one table with the same fields and distinguish each report's data with the ReportType field.   There may be an additional report added to this project in the futre. 

If I went with the 4 tables idea - then it would be 5 tables when a new report was added, but each report's data would be exclusively contained in its respective table.  Or I could have the one table idea, where it would not matter how many new reports are added.  It sounds like the one table idea may be the way to go, but for clear separation of report data would it make any different if I had individual tables for each report's data, or is the one table for all  reports of my project the way to go since the data is basically the same format- just different reports?  I'm just not completely clear on the line of thinking for something like this.

Thanks

 

---

Rich P

Answer 1

Incase it makes a difference, the data for the excel project comes from another program (which I create) and is hand entered into Excel (which now includes the human data entry error element - but that's how I inherited this thing).  So I would have to modify the source program to write to the sql server table for this report.   And the data changes by the day - you get one set of numbers today but tommorrow that set is gone - like water under a bridge, clouds in the sky.  So do I want to write to 4 separate tables or write everything to one table?  I don't think this is referential integrity related.  Now after writing my posts (and reading them over) I think I will go with the 4 separate tables.

---

Rich P

Answer 2

4 tables would make it good manageable and less records in each table would speedup the result and would avoid locks.

 

 

/* Please click "Mark As Answer" on post if answers or "Vote as Helpful"*/

---

Trilok Negi

Answer 3

We do not write reports in SQL; we write queries that are then passed to the front end. You should have one base table with the four reports being done with VIEWs, if the calculations are tricky. 

Keeping four separate tables is like wearing four wristwatches. With a global time signal, you are never sure which ones is right. 

It would also be nice to have a proper data entry procedure that woudl protect you from bad data. Spreadsheets were never mean tfor this purpose. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

I think I will try both approaches -- 4 separate tables and the one table approach which would contain all the data for all the reports.  Which ever approach is less of a headache to manage -- I will go with that approach in the final run.

---

Rich P