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.