I'm trying to decide the appropriate way to design my tables to store my genetic data. Basically, I have samples, and each sample has multiple loci (a locus is basically a point on the genome), and each locus has a pair of values representing different alleles, or variants (basically, one allele is obtained from the father, and one from the mother).

There are potentially dozens or even hundreds of loci, depending on the project. The two alleles may or may not have the same value, and there is no meaning in the order they appear.

This is what the raw data might look like, and is also typical of what some programs expect for import: enter image description here

On the plus side, it's still relatively easy to visually interpret and manually enter or change individual values without some other tool. On the negative side, there is no relationship between allele1 and allele2, they just get entered in a random order. This can make querying the data somewhat more cumbersome in some situations, and ends up involving a union. What really bothered me, though, was that it just doesn't seem to meet normalization rules with having alleles spread between multiple columns. It'd be like having pet1, pet2, pet3... as columns in a table listing owners and their pets.

So, I came up with this, which is what I'm currently using: enter image description here

I like it more because it seems like it's properly normalized, but the downside is that it is visually harder to work with. I don't mind this, because although I'm a biologist, I have a relatively strong CS background and am used to thinking about data abstractly. But my colleagues do not have that background, making it harder for them than it is for me.

Ultimately, I can write scripts to convert the raw data to the proper format for import, as well as converting data exported from the table for use in other programs, but this all depends on finalizing the table design. So, have I properly designed the table for this data? Or am I mistaken about it being normalized? Is there some other alternative I'm missing?

Thank you.

1 Answer 1


Your first attempt makes by far the most sense - (the one with 4 fields).

enter image description here

As a rule, tables should be "tall and slim" rather than "short and fat"

It means that you can easily compare different alleles (for the same locus) belonging to the same individual - which is MUCH more difficult with your second schema.

The second schema means that you will have to use windowing/analytic functions far more frequently - and if you're using MySQL (which is very common in biology unfortunately) you won't have that facility.

Speaking as someone with both genetic and computer science degrees, I'd go with the first schema - I've worked with similar data (FragileX) - comparing different alleles - it was important for us to distinguish the father and the mother (we were doing multi-generational pedigrees).

I would recommend you include (arbitrary) - allele1 as the father's and allele2 as the mothers as part of your design - you never know when this knowledge might come in useful. Maybe you could add another field (isParentKnown) so that you can distinguish between those samples for which the parents are known from the ones for which they are not.

  • What if I don't have pedigree information, and cannot determine how individuals are related? For context, I'm a new MS student in wildlife ecology, so our work is with wildlife populations, and although we may in some cases capture enough individuals to piece some parent/offspring relationships together, it's unlikely in general. If I wanted to try and incorporate that information in this case, it seems like my second schema might be better, because I can then have 'Father', 'Mother', 'Unk1', 'Unk2' as values for the Allele field. Would that change your recommendation?
    – anjama
    Nov 19, 2015 at 18:38
  • Also, it looks like it should be relatively straightforward to create one of the schema's as a table, then the other as a view based on that table, so I'm not too worried about which one is better for data analysis down the line, more which is better from a pure database design principle, and if something about the alternative is enough to still justify using it instead.
    – anjama
    Nov 19, 2015 at 18:45
  • If you're using MySQL (are you?), constructing that view will be difficult. I still vote for the first attempt as the better one from a "pure" database design perspective, and already having experience with such matters, I'm fairly sure of my ground.
    – Vérace
    Nov 19, 2015 at 19:06
  • In response to your not knowing which is the parent, you could have, as I said, isParentKnown (Boolean) with N for anonymous samples and Y in cases where they are known. (Sorry about the late answer - but I didn't see your post about being a new MS student until just now - for some reason, it only popped up now). The 'Unk1', 'Unk2' is not necessary and the first schema is still the one to go for IMHO.
    – Vérace
    Nov 20, 2015 at 0:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.