CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Pluto035
Pluto035

Reputation: 23

Database Design For Recording Test Results

I work at a manufacturing plant where we assembly 10 different products. Each product is similar in function but requires different parameters to be tested. Originally I created an Access database to store our test results for each unit we build. I laid out the database by having one table for each product. This table stores the production ID along with the test parameters (pressures, temperatures, pass/fail information.. etc.) I feel like this was a poor way to approach this but it seemed to be the only way I could use access's bound forms for easy data entry. My problem is that now whenever I need to add a new test parameter I have to change the table design as well as the forms.

Soon I will have the ability to recreate this system in mySQL and I'm hoping there is a better way to approach storing these tests results. Any insight would be very useful.

Thanks.

Upvotes: 1

Views: 6088

Answers (4)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

I would use (at least) the following tables:

Product

Id, Name, TestSchedule

Analysis E.g. Measurement of temperature with normal operating parameters with a 1 Kelvin fault tolerance

Id, Name, Description, Instruction

Test E.g. Temperature measurement in product p, expected result is 300-360 Kelvin.

Id, ProductId, AnalysisId, LowerLimit, UpperLimit

TestResult Test result for batch X, e.g. 342 Kelvin, pass

Id, BatchId, TestId, Result, Status (pass/fail)

The reason for having both an Analysis table and a Test table is normalisation. The analysis is generic, specifying a method. The test specifies acceptable limits when the analysis is performed on a particular product.

Upvotes: 2

jfmatt
jfmatt

Reputation: 926

Look up "database normalization."

At the most extreme, you could split it into 4 tables:

  • Product_Types: Product type (VARCHAR/CHAR), id (INT)
  • Products: id/production id (INT), product type (INT, foreign key bound to Product_Types.id)
  • Test_Parameters: Type (VARCHAR/CHAR - pressure, temp, etc), id (INT)
  • Test_Scores: Product (INT, foreign key to Products.id), test (INT, foreign key to Test_Parameters.id), score (INT/whatever seems appropriate), timestamp.

You could theoretically do without the first and third tables and instead just have the names saved in each record (i.e. Product entry: id = 12345, type = "chair"). It's very slightly faster for retrieval that way, but it's also not robust against people misspelling things (i.e. select * from products where type="chair" will miss an entry with type="chiar"), and takes up more storage space since you're saving the textual name over and over again.

Regardless, this is the basic model for a many-to-one relationship, which is what you're looking for: one product, many tests (or, with all four tables, many-to-many: many products, many test types). You need them in separate tables, with each product given an id, and then a foreign key to link each test result to the product it applies to.

Now, let's talk about constraints.

One that I would probably think about throwing on would be a unique key on the test-result table that indexes both the product id and test type, and then be sure to use "ON DUPLICATE KEY UPDATE" so that old values are overwritten by newer ones. That way, you're certain to only ever have one result for each test for each product. If you want to keep old records as well, disregard this paragraph.

The one thing you will definitely lose is the ability to require that all tests are done for a given product. That much will have to be done outside of the database. If you want to require that all the columns are filled in for every single product, then you have to do it pretty much the way you've been doing it (one column for each test in a colossal unified table with NOT NULL constraints on every test column), because now the test results and object id are functionally dependent on each other (neither can exist without the other).

Upvotes: 2

Limey
Limey

Reputation: 2772

I think you are looking at needing to use a Many To Many Table.

So One table that stores your products, one that stores each unique test, and then a third M2M table that links product A to however many tests it needs. you M2M could also store (generically) your test results.

Upvotes: 1

ethrbunny
ethrbunny

Reputation: 10469

Create a table of products with a unique ID / product. Then create a table of tests with a unique test id and a column for applicable product(s). Join these to find which tests apply to which products. You can add new tests at any point.

Further you could have a 'test version' column if you want to store test history, results, etc.

Upvotes: 0

Related Questions