Check Database Relationships!

drumdogg

drumdogg

drumdogg

Associate
Joined
6 Mar 2009
Posts
495
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
 
Hi,

The first 2 tables look fine, but it isn't practical to have a table for every single product. I'd have 4 tables: 'products', 'testing', 'product_tests' and 'product_retests'. This way you can have all product tests and retests information in the same table, you'll just need to pass in the product_id so you know which product it relates to:

products
-----------
product_id (PK)
product_name

testing
--------
testing_id (PK)
product_id
testing_name

product_tests
----------------
product_test_id (PK)
product_id
testing_id
test1
test2
test3
test4
date

product_retests
-------------------
product_retest_id (PK)
product_id
test1
test2
test3
test4
date

Jim :)
 
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:(
 
In that case, i would just add another table for storing test info ('test_info'). Give it a primary key of 'test_info_id', along with any other key fields to link it('product_id' etc) and any additional fields (e.g. 'test_name').

That way, it doesn't matter if there is 1 entry or 1000.

Does that make sense?

Jim
 
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??
 
Basically, have a 'test_info' table with stuff like name etc in it, whatever fields you require, then insert a row into this for each test that's required (whether it's 6 or 8).

So instead of having columns for tests in a particular table, have them as individual rows within a separate table (test_info)
 
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??
 
Am I right in thinking that if you are adding test information, it will be one row at a time, so Test 1 with a specific name one week, Test 2 with a different name the next and so on??

If that's the case, have the 'name' as a column, and then insert a row into this table each time you add test information
 
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.
 
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?
 
do products have different test names then? I was under the assumption the test names carried across all the products?

You'll need a column for product_id in the test_info table so that you can relate the 2
 
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.
 
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.
 
Sounds like you need to link specific tests to products, so link them something like this:

product
--------
product_id (PK)
product_name


tests
---------
test_id (PK)
test_name
test_details
(any other details relating to the test)


products_tests (this will link which product has which tests, one row for each product/test pair)
--------------
products_tests_id (PK)
product_id
test_id


test_results
-----------
test_result_id (PK)
products_test_id
test_result_status
test_result_notes
(plus any other relevant details, date/time etc)



This will allow you plenty of flexibility, and if one test is performed on multiple items, you can look it up, and it won't limit you from adding/removing tests in the future (especially if you add a column to the products/tests etc for whether they're active and can be used)
 
You must log in or register to reply here.
Back
Top Bottom