I'm designing a database to hold (biological) experimental data. The database needs to be flexible enough to data from different experiments - so some experiments measure relative levels of protein whereas others measure amount of mRNA. My question is essentially on good database design. Here is what I currently have (using Microsoft Access for visualization purposes only - I'll implement with pythons sqlite3 )

enter image description here

This structure will work well with a specific experiment called RPPA which is a DetectionType from the experiment table in the below schema. RPPA uses antibodies which are bought from companies and target specific proteins which are encoded for by specific genes. Western blots are another type of detection which use antibodies. The problem is that data from western blots doesn't have all the extra statistics in the raw data, so in the present scheme, if I have a 'western blot' experiment, all these extra fields that correspond to RPPA statistics must be blank. Is it good practice to build a database in which some of the fields are meant to be blank depending on the value of a field in another table?

Another problem is that if I change the detection type field to PCR or WaferGen, both of which measure biological components are the mRNA level rather than protein, then the Antibodies table does not work (because these methods complementary sequences rather than antibodies).

One idea to fix this problem is to further abstract the Antibodies table to a Targets table instead:

enter image description here

But I'm finding I have a similar problem as above. In the Targets table, the Type field can be either 'protein' or 'mRNA'. If this type is protein, then I need fields to hold the AntibodyName and Company but these are not needed if the Type is 'mRNA'. Is it good practice to build a table this way, i.e.when if the value of one field is 'x', several other fields must be NULL?


I'm a PhD student and I'm receiving a lot of data that were collected in biological experiments. This data is not only big (big for me but not as big as most of you are used to I'm sure) but of heterogeneous types - by this I mean the experiments measure different biological components, namely different species of protein and mRNA. I need a way of storing this data that is readily accessible for performing computation (hence the use of Python sqlite3's API). The database is in essence going to form the the back end of a Python package or application for data analysis.

Information about the data

  • Cells are grown for a few weeks then treated, collected and stored until a detection experiment is conducted.
  • A detection method can be RPPA or Western Blot which are techniques in molecular biology for measuring protein or 'WaferGen' or 'PCR' which measure mRNA. One major difference between mRNA or protein experiments is that detection of a protein uses 'antibodies' whereas for mRNA requires 'primers'. These are ways of targeting the respective biological components but they are mutually exclusive, i.e. samples used for targeting mRNA cannot be used to look for protein.

  • Both antibodies and primers correspond to a Gene which is officially represented as a Gene Symbol, for example the SMAD3 gene has a primer and an antibody.

  • Antibodies can have post-translational modifications or PTM for short whereas primers cannot. PTM's are like on or off switches in biology, so but only occur at the protein (not mRNA) level.
  • Primers are short sequences of DNA specific for a gene. There are only 4 'bases' to choose from so ATGCGTGCA would be an example.


  • An experiment produces many samples
  • A sample belongs to a experiment
  • A sample can only have one detection method
  • A detection method can be used in multiple experiments
  • If an experiment has a detection method targeting protein it requires an antibody
  • If an experiment has a detection method targeting mRNA it requires a primer.
  • A sample can be probed by multiple antibodies or primers at once


  • While RPPA data implicitly comes with the various statistics shown in the bottom 8 fields of the Samples table above, western blot data does not and only has an RFI field - which is code for the amount of signal detected. Therefore, If I set the DetectionMethod to WesternBlot the other fields that deal with RPPA statistics must implicitly be empty. This doesn't strike me as good database design.
  • If a sample is from an mRNA experiment I need to be able to query information about the primers whereas if the sample is for protein I need to get information about the antibodies used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.