Check Database Relationships!

drumdogg · 10 Sep 2012 at 08:44

Hi Guys, i am setting up a mySQL database which will hold testing results on various products.

Quick overview of the system. There are around 30 products which are made and will be tested once production has finished. Each product will have different tests done on it depending what product it is. (So i think each product should have it own testing table with all its results). If products fail the testing requirements then a retest will have to be submitted. So therefore each product should have its own retest table with its results as well. Quite new to the whole database relationship stuff so here is what i think it should be like, but please correct me or help if possible.

Code:

products
--------------------------------------------------
product_id | product_name| Testing_id | Retest_id
-1         | product1    | 1	      | 1
-2	   | product2    | 2	      | 2
-3         | product3    | 3	      | 3

Testing
---------------------------------------------------
Testing_id | Testing_name | product_id
-1         | product1Test |     1     
-2         | product2Test |     2
-3         | product3Test |	3

Product1Test
-------------------------------------------------
Date       | test1 | Test 2 | Test3 | Test4 | Testing_id 
......     |       |        |       |       |	1
......	   |       |        |       |	    |
......     |       |        |       |	    |

Product1Retest
------------------------------------------------
Date       | test1 | Test 2 | Test3 | Test4 | Retest_id
......     |       |        |       |       |	1
......	   |       |        |       |  	    |
......     |       |        |       |       |

Thanks

drumdogg · 10 Sep 2012 at 10:16

Hi Jim, thanks for the reply.

I know what you mean and might go down that road now but the only thing is that every product has a different amount of tests. One may have 6 and the other may have 8, and also different test names(column headers).

Not sure how that would work then is the only thing

drumdogg · 10 Sep 2012 at 10:39

Sorry but don't quite get what you mean.

Do you mean set up the main table with say 6 tests and for the products that have 8 then relate the other two tests to the test_info table??

drumdogg · 10 Sep 2012 at 11:14

Ok Jim, think i understand but forgive me if i wrong lol

In the test_info table add in all the product names(product_id) as columns and then in the rows add in the amount of tests that each product has??

drumdogg · 10 Sep 2012 at 11:20

There will be data inserted into it every week and will also be reading data from it with a search function.

drumdogg · 10 Sep 2012 at 11:29

Every week the same tests are done on the products. Product 1 may have 4 test and product 2 may have 8 tests. The test names will stay the same. The products are put through different tests, so 'Test 1' just stands the name of the test that is being carried out. If you know what i mean.

drumdogg · 10 Sep 2012 at 11:38

Ok thanks for the help

So if i name that columns after the product name how would i relate it to the product_id then. Sorry if thats a silly question?

drumdogg · 10 Sep 2012 at 11:52

Yes each product has different test names but i was going to name each test as t1,t2,t3,t4 and so on just to keep it simple. Some of the test names have special characters in them that php variables dont like.

drumdogg · 10 Sep 2012 at 12:08

Yes each product has different test names but i was going to name each test as t1,t2,t3,t4 and so on just to keep it simple. Some of the test names have special characters in them that php variables dont like.