Click here to Skip to main content
15,119,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Okay so here is a brief description of what I'm trying to accomplish:

Our company currently only sells one kind of product, however we're starting to branch out and begin selling more variations of it. I'm in the processing of creating new software to replace all of the companies old software. The new software will be able to account for any new product that the company may sell in the future. One major problem is that many of the employees are older and don't want anything to change. Many of them aren't very computer savvy so I have to design a powerful, but also simple tool that they can understand. I'm in the process of creating replacement software for the Quality Control department and their current software displays all data in a single DataGridView, because they currently only use one table to hold all of the product's testing results. For the new software I've decided to go with a main table (Product), a table that displays different testings (ProductTestings) and a junction table that links the Product table to the ProductTestings table and contains a result of the testings.

So it looks similar to this:
HTML
<ProductTable>
         <Column1> ProductID </Column1>
	 <Column2> ProductDescription </Column2>
	 <Column3> Etc. </Column3>
</ProductTable>

<ProductTestingsTable>
         <Column1> ProductTestingsID </Column1>
	 <Column2> ProductTestingDescription </Column2>
	 <Column3> Etc. </Column3>
</ProductTestingsTable>

<TestingResultsTable>
     <Column1> TestingResultsID </Column1>
     <Column2> ProductID  --> (Links to ProductID in Product Table) </Column2>
   <Column3> ProductTestingsID-->(Links to ProductTestingsID in ProductTestings)</Column3>   
     <Column4> Result --> (The Result of the specific testing)</Column4>
</TestingResultsTable>


I figured with this approach I wouldn't have to create a new table each time new product is added to the companies inventory. Also, some types of product will require multiple types of testings for the same product. So doing it this way, employees can create a ProductTesting for what they want to test the product for. And then assign the testing to a product and record the result.

Everything is going good so far but the problem I'm having is finding a logical way to display the Product Table and the TestingResults table in one data grid view.

I've tried querying data from the Product Table and joining the TestingResults Table by the common ProductID column.

BUT there is a problem. Most Product will have many different tests done on it. Not just one. So I get a record for each test, instead of a single record that has all the tests joined to it as if they were their own columns.

So if I have:

~~~~Product Table~~~~~
HTML
<Row>
     <ProductID> 1 </ProductID>
     <ProductDescription> ThisIsAnExample </ProductDescription>
</Row>


~~~~ProductTestings~~~~~
HTML
<Row>
     <ProductTestingID> 1 </ProductTestingID>
     <TestingDescription> TestOne </TestingDescription>
</Row>
<Row>
     <ProductTestingID> 2 </ProductTestingID>
     <TestingDescription> TestTwo </TestingDescription>
</Row>
<Row>
     <ProductTestingID> 3 </ProductTestingID>
     <TestingDescription> TestThree </TestingDescription>
</Row>


~~~~TestingResults~~~~
HTML
<Row>
     <TestingResultsID> 1 </TestingResultsID>
     <ProductID> 1 </ProductID>
     <ProductTestingID> 1 </ProductTestingID>
     <Result> ResultForProductTesting(TestOne) </Result>
</Row>
<Row>
     <TestingResultsID> 2 </TestingResultsID>
     <ProductID> 1 </ProductID>
     <ProductTestingID> 2 </ProductTestingID>
     <Result> ResultForProductTesting(TestTwo)</Result>
</Row>
<Row>
     <TestingResultsID> 3 </TestingResultsID>
     <ProductID> 1 </ProductID>
     <ProductTestingID> 3 </ProductTestingID>
     <Result> ResultForProductTesting(TestThree)</Result>
</Row>


Then I would Get:

~~~~DataGridView~~~~
HTML
<Row1>
     <ProductID> 1 </ProductID>
     <ProductDescription> ThisIsAnExample </ProductDescription>
     <Result> ResultForProductTesting(TestOne) </Result>		
</Row1>
<Row2>
     <ProductID> 1 </ProductID>
     <ProductDescription> ThisIsAnExample </ProductDescription>       
     <Result> ResultForProductTesting(TestTwo) </Result>	
</Row2>
<Row3>
     <ProductID> 1 </ProductID>
     <ProductDescription> ThisIsAnExample </ProductDescription>
     <Result> ResultForProductTesting(TestTwo) </Result>	
</Row3>


Instead of:

HTML
<Row1>
     <ProductID> 1 </ProductID>
     <ProductDescription> ThisIsAnExample </ProductDescription>
     <Result> ResultForProductTesting(TestOne) </Result>		
     <Result> ResultForProductTesting(TestTwo) </Result>	
     <Result> ResultForProductTesting(TestThree) </Result>	
</Row1>


NOTE: I'd like to use the "TestingDescription" for the header of the TestingResults column.

I'm using Visual Studio 2012 and programming in C#. I've seperated my business, data access, and presentation layers into three different projects. I'm using the Entity Framework and LINQ to Entities for most of the data management. However, I am using other methods like stored procedures and datasets where necessary, if I can't accomplish what I need with LINQ to Entities. The database is in Microsoft SQL 2012.

So my questions are:
1. Does this approach make sense? And will it work?
2. If it doesn't then what is a common way of dealing with this issue?
3. If it does, then what am I doing wrong and what do I need to do?

Thanks for any and all help. Its very much appreciated.

One Last Side Note: I do understand and have a way around the problem of product having different types of testings recorded for it. Basically, I have another table that groups my testings into structures. So for instance they create a new structure and check 4 ProductTesting rows to be in that structure. Now they can query all product that has been assigned that testing structure. So they only view product in that particular structure and they can see other structures with a combo box. This structure method has worked good for me so far.
Posted
Comments
Tom Wauters 11-Jun-13 16:35pm
   
I had a similar problem once (i think).
What i dit was:
I jammed all the data in a List<t> (to minimize the trips to the server)
and itterated through every cell of the datagridview and looked at
the first cell of each row and got the matching data in the list, and
set the data in each cell seperate.
My list wasn't very big +/- 300 records, it worked pretty fine.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900