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?

If I had to design the database I would have done it in following manner.
Project (ProjectId,ProjectName,Status)
1,Project A, Open

Zone(ZoneId,ZoneName,ProjectId,IS_VARIABLE)
1,ZONE A, 1, 0
2,ZONE A, 2, 0
3,ZONE A, 3, 0
4,ZONE A, 4, 1

Reading_hdr(ReadingIndex,date,ZoneId)
1,27-mar-2008,1
2,28-mar-2008,4

Reading_dtl(ReadingIndex, srno, ReadingType, F1, F2, F3)
/* Reading type could be in, out or fix. no extra columns are required
I am assuming database is mysql so you can set enum type.
*/
1,1,fix,23,53,54
2,1,in,30,63,63
2,2,out,30,69,64

This table you can join using join operators, can using case when then statement you can create a crosstab query.

Primary keys are shown in bold letter
Foreign keys are shown in italics

If I had to design the database I would have done it in following manner.
Project (ProjectId,ProjectName,Status)
1,Project A, Open

Zone(ZoneId,ZoneName,ProjectId,IS_VARIABLE)
1,ZONE A, 1, 0
2,ZONE A, 2, 0
3,ZONE A, 3, 0
4,ZONE A, 4, 1

Reading_hdr(ReadingIndex,date,ZoneId)
1,27-mar-2008,1
2,28-mar-2008,4

Reading_dtl(ReadingIndex, srno, ReadingType, F1, F2, F3)
/* Reading type could be in, out or fix. no extra columns are required
I am assuming database is mysql so you can set enum type.
*/
1,1,fix,23,53,54
2,1,in,30,63,63
2,2,out,30,69,64

This table you can join using join operators, can using case when then statement you can create a crosstab query.

Primary keys are shown in bold letter
Foreign keys are shown in italics

Thanks for the ideas. I will give it a try and let you know in a day or two how it goes.

Thanks.

artrivedi,

can you tell me what the field 'srno' in the readings_dtl table is for?

Thanks

It is just sr no for readingindex 1,2,3.... etc
If you are not comfortable with it then you can generate new primary key reading_srno (This will go from 1,2,3......n regardless of readingindex)
fk_readingindex is fk from header table

new design
Reading_dtl(Reading_srno,fk_ReadingIndex, ReadingType, F1, F2, F3)

Primary keys are shown in bold letter
Foreign keys are shown in italics

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.