one table per data type?

[kman42]

I have some company data that is collected on several different types of reports, Annual Report, Final Report, etc. These reports have different formats and collect different, but overlapping, information. For example, the number of employees and the company's revenue are collected on the annual and final reports, but number of patents is on the final report and other reports, but not on the annual report.

I want to be able to do some calculations on the number of employees, revenues, and patents over time so I thought that I would make them their own separate tables and have them related to the report table and the company.

tbl_Reports
pk_ReportID
fk_CompanyID
ReportType
DateOfReport

tbl_NumberOfEmployees
pk_NumberOfEmployeesID
fk_ReportID
fk_CompanyID
NumberOfEmployees

tbl_Revenue
pk_RevenueID
fk_ReportID
fk_CompanyID
Revenue

tbl_NumberOfPatents


pk_NumberOfPatentsID
fk_ReportID
fk_CompanyID
NumberOfPatents

There are actually 6 different report types and more than 30 types of data that are mixed and matched on the different reports. I want to be able to look at each of these pieces of data over time for a company, e.g. how the number of employees changes over time. It seemed that this was a reasonable approach, but now that I am creating a bunch of tables for each data type, I'm not so sure.

When I create a new record on the Final Report form, I need it to create records in tbl_Reports, tbl_NumberOfEmployees, tbl_Revenues, and tbl_NumberOfPatents. I think this is done with a one-to-one relationship where pk_ReportID and fk_ReportID would both be primary key fields. But I know one-to-one relationships are rare, so I'm not sure if I am going about this the right way.

Any advice?

[June7]

So you are not creating these financial reports but mining the summary data and putting that into a database. You could put all this in one table but then would have a lot of empty fields. Though not fully normalized that might not be all bad, especially for a very small db. It can simplify data entry. If you want the db technically normalized, I think you are on right track. The one-to-one relationship is appropriate because the different reports will not all have the same data. A query that joins all the tables will give the effect of one table with lots of empty cells.

I don't see need to duplicate the CompanyID in every table. This info is available by link to tbl_Reports.

So you must balance technicality of design with ease of data entry and manipulation and decide which way to go.