Relational Database Design Structure help

[DoDo1975]

Hi,

I was hoping someone might be able to give input as to the best database structure to efficiently store the following information.

The database is to be used to store laboratory results for multiple sites.

Information:

I have multiple sites(1000) with site specific data like name, lat, long etc. Each site could have many different samples (50-200) taken at it at various different times. Each sample taken could have results for 1 or more of 260 different tests. Each sample also has about 20 fields of information aside from the lab results like date, location, sample_id etc.

Presently, I have set my relational database up as follows, but would like to hear other options or ideas that might make it easier to write queries for it and make it faster to search.


Site Table: Includes Site specific information, primary key Site_ID

Linked to Sample Table on site_ID: contains sample specific information, primary key sample_ID

Linked to Lab Results table on sample_ID: contains sample_ID, test_name and the result of the test.

The Lab Results table is also linked to a fields table which contains the names of all the test_name’s with their units and groups. This is to ensure that each test_name exists and to make it easy to change the test_name, or select all results from a specific test group.

In its present for it was difficult to create a crosstab query to return a sample_id with all its corresponding results across the horizontal. It would have seemed to be easier to leave all the test results in one very large table as the sample ID and test_name would not have to be repeated over and over, but this was also very cumbersome.

Does anyone have a better idea on how to do this or how to speed it up and make it more efficient. An help appreciated.

Joel Lindsay
Vancouver, BC

 

[seagoing]

Seems to me your structure is correct. I've been involved in building LIMS (Laboratory Information Management Systems) for some UK Based Water Supply companies and essentially the structure we used was the same,
Sites -> Samples -> Results and the lookup table you call test_names we refered to as Determinands.

It might be worth considering programming some custom reports to give you the list of sample results in the form you need. Standard Cross Tab Queries are great when your data neatly adapts to them otherwise it might just be better to program your own output.