Relational Database Design Structure

Results 1 to 2 of 2

Thread: Relational Database Design Structure

  1. #1
    Join Date
    Dec 1969

    Default Relational Database Design Structure

    Hi,<BR><BR>I was hoping someone might be able to give input as to the best database structure to efficiently store the following information.<BR><BR>The database is to be used to store laboratory results for multiple sites.<BR><BR>Information:<BR><BR>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.<BR><BR>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.<BR><BR><BR>Site Table: Includes Site specific information, primary key Site_ID<BR><BR>Linked to Sample Table on site_ID: contains sample specific information, primary key sample_ID <BR><BR>Linked to Lab Results table on sample_ID: contains sample_ID, test_name and the result of the test.<BR><BR>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.<BR><BR>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.<BR><BR>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.<BR><BR>Joel Lindsay<BR>Vancouver, BC<BR>

  2. #2
    Join Date
    Dec 1969

    Default It looks good to me, except...

    ...that you probably should have used test_id instead of test_name in the labResults table.<BR><BR>But if test_name is the Primary Key in the "fields" table, then you&#039;ve certainly normalized all this properly.<BR><BR>What do you mean by "difficult"?? The query looks simple enough, given that DB structure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts