I have a db, that I feel is efficient, but I have trouble displaying the data the way I want. I can either keep the db and change the display, or I can redesign the db and get the display format I want.
I'll try to explain my situation and hopefully somebody can suggest something. It'll be lengthy, but I want to make sure everybody understands it.
My web app is a tracking system for a certain industry. My users can have many projects. During a project, the user will make several 'readings'. And each 'reading' will have many 'data points' (for lack of a better descriptor). For example, the user will go to the site, record a bunch of measurements, and enter them into the system. They will do this an average of 3-4 times for each project.
'Data points' are a bit complex also. Let's say each 'data points' are comprised of multiple 'zones'. Three of these zones are fixed (i.e. all projects will have these three zones). There also exists at least one 'variable zone'. Variable zones are all the same, they just differ in there physical locations.
Now, in each 'zone', the user will record the same three fields. This is regardless of the whether the zone is fixed or variable. However, a variable zone has two parts, In and Out. They each have the same three fields as the fixed zones.
So, let's recap the system. The user goes to the site. He gets out his equipment and begins taking measurements. He goes to the first fixed zone. He records three bits of data. Then he moves onto the second and third zones and records the same three fields. He has two variable zones, and he goes to them and records the three 'In' fields and the three 'Out' fields for each zone.
So, this is how I set up the db. I have a 'project' table. It is linked to a 'readings' table via a foreign key. All linked tables have foreign keys. The readings table stores the date of the reading and the three fields for the fixed zones. I also have a 'variable_zone' table. It stores the variable zones and their names. The zone table is linked to the 'project' table. Finally, I have a 'variable_zone_ readings' table. It is linked to the 'variable_zone' table and the 'readings' table.
If there is one variable zone, the 'variable_zone_readings' table stores one row for now. Remember the In/Out of variable zones. So there are six fields. As of now, I can either have all six fields on one data row, I can add a new row for each reading. Each row will be designated 'in' or 'out'.
So here's an example of a how a reading will be stored in the db.
Project table
| project_index | project_name | status |
| 12 | Project A | Open |
Variable_Zones table
| zone_index | fk_project_index | zone_name |
| 5 | 12 | Zone A |
| 6 | 12 | Zone B |
Readings table
| readings_index | fk_project_index | date | fixed data 1| ..|2|...|3|
| 1 | 12 | 01/01/2009 | 25 | 20 | 15 |
| 2 | 12 | 01/02/2009 | 26 | 21 | 16 |
Variable_Zones_Readings table
| var_index | fk_readings_index | fk_variable _zone_index | in_field_1 | ...|2|...| out_field 1 |...|2|...
| 3 | 1 | 5 | 75 | 56 | 65 | 45 |
| 4 | 1 | 5 | 88 | 34 | 44 | 77 |
| 5 | 2 | 6 | 77 | 57 | 66 | 46 |
| 6 | 2 | 6 | 89 | 35 | 45 | 78 |
When the user is done for the day, I want him to be able to view the project and all of his readings. Remember that he visits this project site every day for about three days.
This is how I would like to display the data.
Field | Date | Date |
Fixed 1 | 01/01/2009 | 01/02/2009 |
field 1 | 3 | 4 |
field 2 | 5 | 6 |
.....
var 1 in
field 1 | 2 | 5 |
field 2 | 6 | 7 |
....
var 1 out
field 1 | 7 | 8 |
field 2 | 2 | 9 |
....
As you can see, new dates are new columns. I can't get it to work like this, but it's what I want. I can easily flip this and display new dates as rows. And that would work.
here's what I can do so far. I can get all fixed zones and fields to display properly. Once I get to the variable zones, I can get the first zone to display properly, but the other zones won't show up. Or I can get it to display all zones, but not the fields.
I tried to fix this problem with my php code, but I haven't go it to work. I do a 'while', but it stops after the first one. I asked for help on a php forum, but I haven't got any responses. So I thought that it might be fixed by redesigning the db.
Maybe I can keep the design and get a better query to organize it the way I need, or something.
Anybody got ideas?