I'm working on system that tags images. I'm going to use the xxHash (lz4) for hashing images. With hashes of size 64 bits, it looks like I won't have a 50% chance of a collision until ~4 billion images are added. At that point I think a collision would be the least of my worries.
The size of my VARCHARs in the images and tags table are not set in stone. The image path can be calculated from the file_hash (to base 36 number string) and the file_extension. file_name is just for preserving the file_name, as the file will be renamed to its base 36 hash. In the future I plan on normalizing the table further, having a table for all the file extensions and then storing the key in the images table. Might be excessive though, as file extensions don't change.
Here's my schema:
CREATE TABLE images ( file_hash BIGINT PRIMARY KEY NOT NULL, file_extention VARCHAR(4) NOT NULL, file_name VARCHAR(64) ) Engine=InnoDB; CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tag_name VARCHAR(64) UNIQUE NOT NULL ) Engine=InnoDB; CREATE TABLE tag_references ( tag_id INT NOT NULL, file_hash BIGINT NOT NULL, CONSTRAINT img_hash_fk FOREIGN KEY (file_hash) REFERENCES images(file_hash) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT tag_id_fk FOREIGN KEY (tag_id) REFERENCES tags(id) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY(tag_id, file_hash) ) Engine=InnoDB;
I'm a little worried about using the BIGINT as the primary key. I was also thinking about using an INT (auto increment) as the primary key and having the hash as just another field marked as unique. I'm not sure how much complexity referring to images by a long will be on the code side either. I guess in some cases reading a long takes more than one cpu cycle, which might introduce complexities in a multi-threaded environment. I don't know if I'll even need to share "references" to images between threads though. I don't want to pre-maturely optimize.