Designing a relational database
2
0
Entering edit mode
7.7 years ago
int11ap1 ▴ 450

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 • 1.7k views
ADD COMMENT
0
Entering edit mode

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

ADD REPLY
3
Entering edit mode
7.7 years ago

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);
ADD COMMENT
1
Entering edit mode
7.7 years ago

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, ...);

ADD COMMENT

Login before adding your answer.

Traffic: 1759 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6