Help structuring DB table for grid that uses 1 DB table for rows and 1 for columns
Hi all,
So I am trying to create a table grid for product testing where each cell in the table has a dropdown for pass or fail.
Each row is a specific test-question.
And each column is a single product.
My db structure is like this:
test hasMany questions
question hasMany products
The issue I can't wrap my head around is that each test-question may only relate to 3 out of the 5+ products/columns, therefore that cell should exist but be blank (without the pass/fail dropdown).
How would you structure your db tables and data loops to accomplish this?
You can achieve this in a single table with fields for product, question, test result, etc.
As for representing this in a grid, just query the table with the necessary sort order and then when you iterate through the dataset you can conditionally create a new row/column as you encounter new products/questions. If you need to force an intersection that doesn't exist in the data, you should be able to make this determination by first establishing a count of questions and products and looping accordingly.
I'm envisioning a nested loop where you're iterating over the maximum number of questions and the maximum number of products. Then, within your loop you use a conditional statement to evaluate whether that question/product combination exists. If it does, you return the test result value. If it doesn't, you return null or some other value that creates a blank cell in the grid.
