Science Lab Database Design

[dsundertaker]

I'm designing a database for our neuroscience lab. Currently, our lab and every other lab in the department keep our data in Excel spreadsheets, which is ridiculous considering the massive amount of data we work with. I'm proficient with SQL but clueless about actually designing an efficient database without redundant information.



I hope to get some help from the pros here.

As always, we're concerned about data integrity so how we capture and manage data must be as efficient and fool-proof as possible.

I hope a logistical explanation will help:

One Experiment has multiple Researchers who are assigned multiple Animals that are ran on multiple (behavior) Tests over multiple Days. As is the case with experiments, Animals are grouped into different Conditions with some animals getting one condition and other animals getting another based on the design. Not every experiment uses each test and the researchers change each semester.

Thus far, I've created tables for Experiment, Researcher, two of our 20 behavioral tests, Animals, and a table for animal information such as sex, mother ID, father ID, birth date, etc. I'm clueless about how I'd efficiently track elements unique to the experiment design such as drug, dose, and vial color codes.

EXPERIMENT

Each Experiment also uses a different design, something I'd also like to track. We use different Drugs and Dosages. Sometimes one Experiment uses two or more Drugs in two or more different Dosages.

We color code the drug vials so the researcher remains blind to what they give the animals. For instance, 50 mg of caffeine may be red, while 100 mg is green, and saline is blue. 50 mg of caffeine + estrogen may be orange, while 100 mg caffeine + estrogen is yellow, and saline + estrogen is black.

When researchers enter their drug data, they only enter color codes which we (archaically) translate by hand into the drug given and by association the condition the animal was in. It would be great to keep track of the drugs, dosage, and vial code colors used in a particular experiment.

ANIMALS

Each animal is weighed on every test day and sometimes multiple tests are given in one day so I assume it'd be useless to have that data in two different Test spreadsheets. On test day, we also record the amount of food and water an animal consumes. Again, to track that info in two different spreadsheets would be bad, right?

In my limited knowledge of database design, I know that Experiment is the table with the primary key but I'm clueless as to which tables to create and how relate the other tables efficiently.

The true end goal is to, of course, streamline data entry so that the forms step the researchers through the process with minimal chance for error. In the end, I'd first like to create a locked form that only I can enter data about the experimental conditions such as which drugs we use, etc.

But I'd also like to create an experiment-specific form that display only the parameters of that experiment for data entry by our researchers.

EXAMPLE

For example, Experiment 0001 has Jane running Animals 101 and 102 (both in Condition 1 with a red vial) on Test 1 on Monday and Test 2 on Tuesday. Joe runs Animals 201 and 202 (in Condition 2 with a blue vial) on Test 2 on Monday and Test 1 on Tuesday. Again, note that we have at least 20 behavioral tests, each with their own particular data collection paradigms, and typically 4 or more conditions per experiment.

With respect to data entry, Jane would type in an experiment number which would open a form with a combo box that allows her to select her name from a list of only those researchers working on that experiment, as well as the ability to enter data only for those behavioral tests specific to it (say with appearing buttons that say Enter Data for X Test or Enter Data for Y Test).

She would click Enter Data for X Test and be taken to a form in which she has to input the animal ID, the color of the vial given that day, and data for that test day. She would have to do that with multiple days of data for X Test, hopefully without reentering the animal ID.

I apologize for the length of this post and I'm certain these design logistics aren't tough to translate into Access, but I'm a rookie at database design. It's amazing how many scientists love and work with data but have no idea how to manage it efficiently, leading to all sorts of problems. I don't want to be one of those scientists.

Thanks in advance for any upper-level, pro help you can provide because I'm definitely at a loss.

[jzwp11]

Welcome to the forum!

The table structure is the foundation for the entire database application, so I strongly recommend spending the time to do it correctly and thoroughly. First, it is best to review the rules of normalization before you go any further.

Also, it is best not to use spaces or special characters in your table or field names. Additionally, you want to avoid using Access reserved words as table or field names. Typically used reserved words are name, date, month, year.

I generally use a primary key field in every table so that Access has a way to uniquely identify each record. The primary key field will also be used to join related tables (primary key->foreign key). I also try to make my tables somewhat generic and leave the specifics for the individual records in the table.

Another thing to keep in mind is that like data should be in 1 table.

Thus far, I've created tables for Experiment, Researcher, two of our 20 behavioral tests, Animals, and a table for animal information such as sex, mother ID, father ID, birth date, etc. I'm clueless about how I'd efficiently track elements unique to the experiment design such as drug, dose, and vial color codes.

(I use various prefixes that help characterize a field, you can choose to use them or not. pk denotes primary key; fk denotes foreign key, dte denotes a date/time field, txt denotes a text field, lng denotes a long number integer field, sp denotes single precision number field. Using a prefix also helps to avoid using reserved words)

Based on the above:

tblExperiments
-pkExperimentID primary key, autonumber
-txtExperimentName
-dteStart (start date)

tblPeople (a table to hold all people including the researchers)
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

It sounds like you have many researchers (people) associated with an experiment. This describes a one(experiment)-to-many(people) relationship. Additionally, I assume that a researcher may work on several experiments over the course of their career. This also describes a one(person)-to-many(experiments) relationship. Having 2 one-to-many relationships between the same two tables constitutes a many-to-many relationship which is handled with a junction table.

tblExperimentPeople
-pkExpPeopleID primary key, autonumber
-fkExperimentID foreign key to tblExperiments, foreign key fields must be long number integer fields to match the corresponding autonumber primary key field to which it will be joined)
-fkPeopleID foreign key to tblPeople
-dteEffective (when the person started working on the experiment)

You will need to have a table to hold the animal data

tblAnimals
-pkAnimalID primary key, autonumber
-fkAnimalTypeID foreign key to tblAnimalTypes not sure how you are defining an animal, genus, species etc.?
-gender
-dteBirth


Since the mother and father of an animal are also animals, they too must be included in tblAnimals. Since an animal can have many related animals (mother and father) and a mother or father can have many offspring, you again have a many-to-many relationship but in this case the two tables are one in the same (tblAnimals), so you still need a junction table.

tblAnimalRelatives
-pkAnimalRelativeID primary key, autonumber
-fkPAnimalID foreign key to tblAnimals
-fkSAnimalID foreign key to tblAnimals
-fkRelationshipTypeID foreign key to tblRelationshipTypes
(since you cannot have two fields with the same name in a table, I used P and S to distinguish the two related animals)

tblRelationshipTypes (just has records such as mother, father, son, daughter)
-pkRelationshipTypeID primary key, autonumber
-txtRelationshipType


You said that an experiment would have many animals associated with it.

You said:

Animals are grouped into different Conditions

An experiment is broken down into many groups

tblExperimentGroups
-pkExpGrpID primary key, autonumber
-fkExperimentID foreign key to tblExperiments
-txtGroupName


I would guess that the types of the conditions (time, temp etc.) will need to be capture along with the specific setting for the condition type.

tblConditionType (i.e. time, temp etc.
-pkConditionTypeID primary key, autonumber
-txtConditonType

Each group in the experiment will have many conditions that are applicable

tblExperimentGroupConditions
-pkExpGrpConditionID primary key, autonumber
-fkExperimentID foreign key to tblExperimentGroups
-fkConditionTypeID foreign key to tblConditionType
-spCondition (the actual time, temp etc.)

Each group will have several animals:

tblExperimentGroupAnimals
-pkExpGrpAnimalID primary key, autonumber
-fkExpGrpID foreign key totblExperimentGroups
-fkAnimalID foreign key to tblAnimals

You will need a table to hold all tests. If you need to, you can have a field that distinguishes the type or category of a test (behavioral, physical, chemical etc.)


tblTests
-pkTestID primary key, autonumber
-txtTestName
-fkTestCategoryID foreign key to tblTestCategories
-fkUOMID foreign key to tblUnitsOfMeasure (a field for the units of measure in which the test results are reported, grams, kg, ºC etc.)

tblUnitsOfMeasure
-pkUOMID primary key, autonumber
-txtUOM

tblTestCategories
-pkTestCategoryID primary key, autonumber
-txtTestCategoryName

I assume that many tests are conducted on an animal

tblExperimentGroupAnimalTests
-pkExpGrpAnTestID primary key, autonumber
-fkExpGrpAnimalID foreign key to tblExperimentGroupAnimals
-fkTestID foreign key to tblTests

Assuming that a test may be run (and results obtained) over several days:

tblExperimentGroupAnimalTestResults
-pkExpGrpAnTestResultID primary key, autonumber
-fkExpGrpAnTestID foreign key to tblExperimentGroupAnimalTests
-dteResult (date of result)
-spResult (single precision number)


With respect to the drugs, we need a table for those

tblDrugs
-pkDrugID primary key, autonumber
-txtDrugName

A table to hold all possible dosages, assuming that the same dosage may be use in conjunction with many drugs. I assume that a dosage has both an amount with a unit of measure and a frequency

tblDosages
-pkDosageID primary key, autonumber
-spAmt
-fkFrequencyID foreign key to tblFrequency
-fkUOMID foreign key to tblUnitsOfMeasure

tblFrequency (hourly, weekly, daily, etc.)
-pkFrequencyID primary key, autonumber
-txtFrequency

Now to take care of your color code which requires a combination of drug and dosage

tblDrugDosage
-pkDrugDoseID primary key, autonumber
-fkDrugID foreign key to tblDrugs
-fkDosageID foreign key to tblDosages
-fkColorID foreign key to tblColors which holds you colors

tblColors
-pkColorID primary key, autonumber
-txtColorName


The above is just a rough structure, and looking at your post a little closer, the conditions table may need to be tweaked since it appears that the tests may be associated with the condition rather than directly with the animal. You'll have to look into that aspect a little more, but this should give you a good start in how to look at your data and extract a structure.

[dsundertaker]

jzwp11,

Your advice is much appreciated. The level of detail in your response was unexpected (and appreciated) and very clear. I hadn't contemplated many of the database table elements that you mentioned, but seeing them spelled out makes perfect since ultrastructurally. I have a paper to submit to a journal by the end of this week, so I'll likely have questions next week, when I get time to implement the suggested design elements.

Again, thanks for such a thorough and understandable response.

[dsundertaker]

I realized that I should provide a more detailed example to give you a better understanding of the challenges in designing a database for our experiments.

We hypothesize that male rats chronically injected with estrogen over 10 days will perform better on a cognitive test after caffeine injection versus controls.

In this particular, simple example, all animals are males and the estrogen dose given each day is constant (e.g., 200 ug per kg of body weight). We take baseline measures on tests before injections start (Day 1). Animals are randomly assigned to two chronic injection conditions: Estrogen or Saline. Animals in both groups get their respective drug in the appropriate amount for 10 days. Each day body weight and food consumed is recorded.

Each animal is tested on Days 5 and 9 of the experiment. On Day 10, animals are injected with their respective drug and the experimental drug (caffeine) or saline. Animals are then tested on the cognitive paradigm. We go from 2 initial conditions to 4 on Day 10. In addition, we may run the test over the next couple days (sans injections) to look for delayed effects.

There are obviously many variations on this model. We can start out with more groups testing different drugs, testing different doses, or testing gender differences between different drugs or different doses. My brain reels.

[jzwp11]

What I provided is just a basic framework. There is obviously more to this database and will require much more analysis and design planning. What I have provided should give you some examples on how to design and integrate other elements. I'll let you digest it for a while and please, if you have questions, post back and we will try to help out. One thing I try to do is keep the table and field names somewhat generic and leave the details for the actual records.