How To Make The Database Structure Based On This Info?

[yoursurrogategod]

Hi all,

 

I've attached a text file of the type of information that I'm trying to capture (for some reason, putting this into code block was impossible, does it matter which browser I'm using?). Basically, what I'm doing is trying to replace a form that's similar to the layout below. The following are the only real differences:

 

* Where it says Date, it will list numbers 1 to 31, representing all of the days of that month.

* The two columns Test Strip result and Temp Begin day will repeat for 16 times.

 

This sheet needs to be generate for every month anew.

 

What I'm having trouble with is trying to figure out how to store this data in a relational database. Any thoughts?

foo.txt

[kicken]

* The two columns Test Strip result and Temp Begin day will repeat for 16 times.

 

Do you mean you have something like this then:

+------------------+------------------+----------------+------------------+----------------+------------------+----------------+
| Date             |Test Strip result | Temp Begin day |Test Strip result | Temp Begin day |Test Strip result | Temp Begin day |
|                  +------------------+----------------+------------------+----------------+------------------+----------------+
|                  |P or F            | C or F         |P or F            | C or F         |P or F            | C or F         |
+------------------+------------------+----------------+------------------+----------------+------------------+----------------+
|  1               | P                | 68 F           | P                | 68 F           | P                | 68 F           |
+------------------+------------------+----------------+------------------+----------------+------------------+----------------+
|  2               | P                | 69 F           | P                | 69 F           | P                | 69 F           |
+------------------+------------------+----------------+------------------+----------------+------------------+----------------+
|  3               | P                | 69 F           | P                | 69 F           | P                | 69 F           |
+------------------+------------------+----------------+------------------+----------------+------------------+----------------+
|  ...31           | P                | 69 F           | P                | 69 F           | P                | 69 F           |
+------------------+------------------+----------------+------------------+----------------+------------------+----------------+

 

If so I'd probably create one table to represent the report as a whole which stores the header information, and a second table to hold the test result/temp/date information with one row per day.  Something like:

 

CREATE TABLE Report (
ReportId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Department VARCHAR(100),
ReportDate DATE,
Solution VARCHAR(100),
Metricide VARCHAR(100),
Other VARCHAR(100)
...
)

CREATE TABLE ReportMeasurements (
ReportId INT NOT NULL REFERENCES Report (ReportId),
MeasureDate INT NOT NULL,
TestStripResult CHAR(1),
TempBegin NUMERIC(4,1)
TempScale CHAR(1)
)