Designing a relational database

Question

[int11ap1]

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.

[mostafa.shokrof]

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

Answer 1

[Pierre Lindenbaum]

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

Answer 2

[dariober]

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