Do i need Relationships between my tables??

drumdogg · 31 Oct 2013 at 13:59

Hi Guys,

Currently I have a database with around 30 tables (for 30 different products) and there is no relationships between any of them just flat tables.

There are testing results stored in each table and I have built a search function to grab the info I want. For example a weeks worth of data inserted.

Currently I have it set up that I have to chose the table I want to search from. The data is then outputted into a table with the table heading hard coded and not being lifted from the database.

Was wondering is there anyway that I could search from all tables in the database and return the info instead of only searching one at a time without any relationships between the tables ??

The main issue I can see is that each table of search results will have to have different table headings and not just plonked into one table and one set of headings

Any ideas welcome

Cheers

drumdogg · 1 Nov 2013 at 19:06

Ok guys, will have to restructure the entire database then. Setting up relationship between tables is new to me but will give it a go.

Cheers for the feedback

drumdogg · 2 Nov 2013 at 18:24

If you don't know about normalization, here is a nice easyish tutorial.

Thanks for this, will have a look through it

Ok, here's a start:

Code:

products
-------------------------
product_id  product_name  test_id
101	    product1	201	

test_info
--------------------------
product_id - test_id - Date - StartTime - Initial - Comments 
101	      201         ...	   ....	    ....	  .....

Ok, the first table would be a list of all the products with an product_id and a test_id that holds the testing info for that product.

The thing that confuses me is that there are obvious testing fields that will be the same for all products, such as Date, StartTime, Initial and Comment. But depending on the product they will also have other fields relating to what tests are done on each product.

For example each product is measured through different sieve sizes such as +1.00mm and +212mm to name a few. So how would I then link these values that will be different across all products??

Thanks

drumdogg · 2 Nov 2013 at 21:00

What is the requirement for data access? How will you be accessing these things? Will there be many dimensions to accessing the data?

There will be MySQL search queries made on the data to get for example, a weeks worth of data or all the tests that have passed or fail on a particular product.

drumdogg · 3 Nov 2013 at 14:50

Based on ur example I would:

Code:
Products
-------------------------
ProductID, ProductName
101, product1

Tests
--------------------------
TestID, Name

TestInfo
--------------------------
TestInfoID, ProductID, TestID, Date, StartTime, Initial, Comments
1, 101, 201, ..., ...., ...., .....

Ok thanks, it looks to be on the right track.

How would link the other aspects of the products testing values.

For example: product1 would also need - 1.00mm, 212mm and 75mm added to it.
Product2 would need 63um and 73um added to it.
Cant see how I would add these because if all the these were added into the TestInfo table then all the product would have to use them.