Coverage table and exons (MySQL)

Question

[Swimming bird]

I am working on a NGS database using MySQL and I found a problem to create the table for coverage data. Each row contains an interval of the entire exome sequenced.

The problem I found is related to the exons because in most cases there is more than one exon and they are separated by commas, for example: "1, 2, 3". As the exons are numbers, creating an extra table is a nonsense and using ENUM is not an option as there is no maximum number. I think the best solution is consider the field as text but, is it correct?

Here you have an example: https://www.db-fiddle.com/f/38SWtKrqWpqZJRVZbw28SC/21

[Pierre Lindenbaum]

Hello Swimming bird!

It appears that your post has been cross-posted to another site: https://bioinformatics.stackexchange.com/questions/8867

This is typically not recommended as it runs the risk of annoying people in both communities.

[Swimming bird]

Ok, sorry. I deleted the post on stackexchange.

Answer 1

[Pierre Lindenbaum]

normalize, normalize, normalize

https://en.wikipedia.org/wiki/Database_normalization

create a table for each chromosome, transcript, exon

[Swimming bird]

Do you mean 3 tables or only one that includes chromosome, transcripts and exons?

[Pierre Lindenbaum]

something like

create table chromosome (id int,name varchar,length int);
create table transcript (id int,chromosome_id int, name varchar);
create table exon (id int,transcript_id int, chromStart int,chromEnd int);
create table coverage(int exon_id int,coverage int);

[Swimming bird]

In case there are 3 exons I suppose there will be 3 different records in the coverage table, right?

Do you know any example of a database that store coverage data? I couldn't find any.

[Pierre Lindenbaum]

In case there are 3 exons I suppose there will be 3 different records in the coverage table, right?

yes