MySQL Forums
Forum List  »  MySQL Workbench

Database design for scientific tests
Posted by: Martin Karm
Date: June 22, 2009 06:44PM

Hello everyone,

I have a problem I've been working on. I have some database experience but I am no expert.

I am trying to create a database for some scientific tests. The basic idea is that there are samples (think rocks taken from different locations) that have tests conducted on them (5 or more different types of tests, each that have some specific data regarding the pre conditions associated with them), and tabular data results from different tests (2 to 8 columns worth of data, depending on which test was conducted).

So, the three main entities are:

-Samples, which have some data specific to them: location, type, etc.

-Tests (performed on individual samples) many different types of tests, many tests could be performed on one sample. Each test will have some data specific to it, such as pressure or date test occurred.

-Test Data (results of different tests). The results could be 2 columns of data or it could be as many as 8 depending on the test that was performed.

My problem is that I can't wrap my head around how to model the tests and test data. Samples are quite easy because it is a usual database type entity.

My question is should each type of test (say TestA, TestB, TestC) all be different tables, connected to their own test result tables? (say TestAResults, TestBResults, TestCResults)

So that it is like:

Sample - TestA - TestAResults
\ TestB - TestBResults

Then for querying purposes find the sample# (pk) in the sample table and then join to TestA, see if sample# (fk) is in there, then join TestB see if sample# (fk) is in there, then join to TestC, see if the sample# (fk) and on and on depending on the number of Tests? This seems wrong to me, but might work?

I've searched for "data model for scientific tests" or "database tabular data" but because tables and tests come up in just about every database topic I can't seem to find any specific data.

Any help is appreciated greatly! Thank you in advance!

Options: ReplyQuote

Written By
Database design for scientific tests
June 22, 2009 06:44PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.