CODEWITHSUNDEEP
sqlmysqldatabase-designdata-structuresdata-modeling
Fabien Barbier
Fabien Barbier

Reputation: 1524

Optimize mysql table?

Here is my actual table schema (I'm using Mysql) :

Table experiment :
code(int)
sample_1_id
sample_2_id
... until ... sample_12_id
rna_1_id
rna_2_id
... until ... rna_12_id
experiment_start

How can I optimize both part : sample_n_id and rna_n_id (all are bigint(20) and allow null=true) ?

About values : we can have : ex : sample_1_id = 2 , Sample_2_id = 5 , ...

Note : values can be updated.

Ideas ? Thanks.

Edit :

I have 3 tables :

Table experiment :
sample_1_id .. sample_12_id (not useful with normalization)
rna_1_id .. rna_12_id (not useful with normalization)


With normalization I should have :

Table Rna :
id
experiment_id
rna_id
rna_name
sample_id (link to a sample in Table sample)

Table Sample :
id
experiment_id
sample_id
sample_name

So, there is a relationship between Rna and Sample table.

Example :

Table rna :
    id =1
    experiment_id = 1
    rna_id = rna1
    rna_name = name1
    sample_id = 2

    Table Sample :
    id = 2
    experiment_id = 1
    sample_id = Sample1
    sample_name = SampName

So, sample_id = 2 => In table sample : id =2 => sample_id = Sample1

Upvotes: 1

Views: 148

Answers (2)

KM.
KM.

Reputation: 103587

normalize your tables like this:

Table experiment :
code(int)
experiment_start

Table sample:
sample_id
code   fk to experiment.code


table rna:
rna_id
code   fk to experiment.code

Upvotes: 0

Pekka
Pekka

Reputation: 449435

I'm not sure what kind of optimization you are looking for but this should definitely be normalized into a table defining experiment-sample and experiment-rna relationships, for example like so:

experiments_to_samples
id  |  experiment_id | sample_id |  


experiments_to_rnas
id  |  experiment_id | rna_id |

that way, every experiment can have an unlimited number of sample and rna references.

This is assuming that there is a table samples and a table rna.

Upvotes: 3

Related Questions