Do i need Relationships between my tables??

[drumdogg]

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

 

[oneilldo]

Why do you have 30 tables? each relating to 1 product?
What DB system are you using?
If the results from each table share some form of common fields, you could frig something horrible along the line of the following. (I would redesign your DB long term though)
1. create a stored procedure where you pass in the search term, say @searchTerm
2. Create temp table with shared fields (maybe product name, date, tableName, etc).
3. insert into this table, select productname, etc from Table 1 where field LIKE @searchTerm
4. Do the above for all 30 tables!
5. At the end of the SP, just select * from the temp table.

 

[Inferno99]

You probably don't need relationships with your implementation. However, you may want to look into data normalisation in relational theory for ideas on improving the structure of your database.

 

[tom_e]

Ideally you'd have one main product table for all your products with all the main product details then other table containing things like sales figures with a relationship to that main table.

 

[AJK]

Hi Guys,

Currently I have a database with around 30 tables (for 30 different products)

OK, that doesn't sound right. Why do you have a table for each product - is this an e-commerce database, or something tangentially related to one? Can you post your database schema?

 

[Addicted]

Oh man that sounds cringeworthy!

Have a look into normalization, you'd no doubt be able to reduce that down significantly

 

[visibleman]

Normalising the data to a relationship schema might not be doable, so there could be a reason why drumdogg never went down that route. Obviously, if the product data is in the e-commerce sense then as others have mentioned, it really should be restructured.

drumdogg - You can use aggregated SELECT's or UNION's allowing to you essentially stitch multiple queries within a single query. However it might not be the most efficient solution depending on what data you're wanting to retrieve, ie - complete tables or select fields within individual tables etc.

 

[drumdogg]

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

 

[djkav]

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

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

 

[AJK]

Just to be clear, a database with a table for each product is not a normalisation issue, it's a fundamental misunderstanding of databases... if that's what actually going on.

drumdogg - we could offer more specific help if you can post your current database schema (ie. what tables you have), or a bit more detail about the problem you're trying to solve?

 

[drumdogg]

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:

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

 

[cats_five]

If each test applies to one product only you don't need test_id in product. Youn join the tables on product_id.

Beyond that, some more info on what the testing is would help.

 

[Dj_Jestar]

Firstly.. lol at recommending a stored proc.

Secondly.. relational normalisation isn't he be all and end all. It is a waste to normalise just for the sake of normalising.

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

A simple key->value storage is sufficient (and quite often optimal) in many scenarios, for example.

 

[drumdogg]

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.

 

[djkav]

Based on ur example I would:

Products
-------------------------
ProductID, ProductName
101, product1	

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

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

 

[drumdogg]

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.

 

[djkav]

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.

Dunno. This is new information. You need to tell us what a test involves, and all the database tables and fields.

What are the product testing values?
Does each test involve one value, or can a test involve multiple values?

Products
-------------------------
ProductID, ProductName
101, product1	

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

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

TestValues
--------------------------
TestValueID, TestInfoID, Value

 

[visibleman]

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.

Are these fields, and if they are is it a simple boolean or?, or data? If it's the latter then i would have thought you'd have a results field and then add the result accordingly. If you have multiple results per test then separate out the results and test information.

If possible (i can understand it isn't always doable), post a few examples of the test data you need to store as it'll help us greatly in being able to advise you the best method/solution

 

[Dj_Jestar]

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.

What queries? What will people be searching for?