MySQL tables relationship and the use of md5 hash

Question

I have a MySQL DB with 2 tables:

• sample_name (stores name of a file, multiple names for same sample_hash);
• sample_hash (stores the hashes of a file, will not store duplicate md5);

(all tables have an id int unsigned NOT NULL auto_increment)

My first option to relate these two tables is by creating an md5 column in both tables and relate them. However this seems to have a downside as I will be duplicating a varchar(32), which can be a waste of space with millions of records.

My second option is to calculate the file hashes first, grab the mysql_insert_id() of the sample_hash table and insert into the sample_name table. This makes sense if the hash in the sample_hash table is new, thus I have the mysql_insert_id() variable at my disposal.

But if the hash already exist in the samples_db, I don't want to store the hash again, so I will have no mysql_insert_id().

Is there an alternative other than searching the id of a given md5 in order to store it in the samples_name table in case the md5 already exist? If so, how can I do that?

Answer 1

From the requirements that you describe, there is no need for the sample_hash table at all.

You can keep the hashes in the sample_name table and do all your lookups of hash values in that table.