This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Designing a relational database

Good morning,

I am designing a relational database. One of the tables contains the coordinates of each gene:

gene_id (key), chromosome, start_position, end_position

However, the start and end position of each gene depends on the assembly they come from. So, I decided to introduce another field:

gene_id (key), chromosome, start_position, end_position, assembly

However, If I wanna put the data of two assemblies into this table, the table is not valid since the gene_id must be unique (key). Do you have any idea to overcome this problem? How would you design the db?

Thanks in advance.

database
• 2,666 views • link
updated by Ram Ram • written by int11ap1 int11ap1

do you want the gene id to be the accession number or other global gene identifiers ?

• 0 views • link
written by mostafa.shokrof • mostafa.shokrof

2 answers

instead of gene_id UNIQUE create a unique constraint. In mysql that would be something like:

alter table GENES add unique index(gene_id,assembly);
• 0 views • link
updated by Ram Ram • written by Pierre Lindenbaum Pierre Lindenbaum

I would go for Pierre's solution. However, an alternative is to add a surrogate key, i.e. a unique identifier which has nothing to do with the data and it is there just to uniquely identify each record. In Postgresql you can add it using SERIAL data type like CREATE TABLE genes (id SERIAL, gene_id TEXT, ...);

• 0 views • link
written by dariober dariober

Log in to answer this question.

More posts like this