MySQL database organization

Question

I have to create a new table in my database. From a form, I'm going to send information to the database:

-Gene name
-Chromosome
-Sequence
-Organism

On the other hand, I have another table (table 1) with user id, user name and password. So I've thought to create 6 more tables in order to organize that information:

-Table2: id_user + id_send
-Table3: id_seq + sequence
-Table4: id_gene + gene name
-Table5: id_chromosome + chromosome number
-Table6: id_organism + organism name
-Table7: id_send + id_chromosome + id_organism + id_gene + id_sequence

Do you think that this organization is correct? Or should I change something?

Many thanks to you, people.

Answer 1

Speaking as recovering biologist....

You seem to be going mad with surrogate keys here - and some of the tables are redundant. Really the database design is not just about how you store information and it's inherent structure but how you intend to access that data - which you've not told us about.

It's far from clear what 'id_send' is supposed to represent.

I suspect (although this is still mostly guesswork) that the structure you need looks more like this:

user (user_id, user name, password, PK(user_id))
sequence (sequence_id (auto increment) gene_name, organism_name, 
  user_id, chromosome, PK(sequence_id))
sequence_data (sequence_id, atcgs)

Splitting the sequence data from it's metadata facilitates performance and also allows for the possibility that you will have genes for the same organism which have different sequences (although you can change this by adding a UNIQUE constraint on the sequence table.

The following tables allow you to restrict input / apply validation / store additional data about entites which you've not included in your description):

genes (gene_name, .... PK(gene_name))
organism (organism_name,.... PK(organism_name))