File name as primary key

database-designprimary-key

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key.

For instance…

"someimage.png" as the PK for table image.

The only downside I can think of is if I had named someimage.png in multiple directories and each directory someimage.png was not the same image. This seems like poor design and there isn't any reason why I'd do this anyways.

Thoughts?

Best Answer

The PRIMARY KEY, or any index for that matter, would be accessed much faster if the length of the PRIMARY KEY was smaller. It is easier to put a 4-byte integer as a unique identified for a fullpath image name than the fullpath filename (of various and ridiculous lengths).

Think of the Clustered Index, where the PRIMARY KEY would reside. Row data will occupy a Clustered Index. In MySQL, the Clustered Key would be coupled with other columns in a nonunique index. Wouldn't a smaller datatype (4 bytes) just make more sense? Otherwise, indexes can blow up at a rate of O(n log n).

To create a unique number for each image, you need a table that resembles something like this:

CREATE TABLE images
(
    image_id INT NOT NULL AUTO_INCREMENT,
    image_name VARCHAR(255),
    image_folder VARCHAR(255),
    PRIMARY KEY (image_id),
    UNIQUE KEY name_folder (image_name,image_folder),
    KEY folder (image_folder)
);

This design

  • allows for multiple files with the same image name, each located in a different folder
  • denies having two files with the same image name in the same folder

From here, just INSERT and retrieve the created image_id as follows:

INSERT IGNORE INTO images (image_name,image_folder)
VALUES ('someimage.jpg','/some/linux/folder');
SELECT image_id INTO @imageid FROM images
WHERE image_name='someimage.jpg'
AND image_folder='/some/linux/folder';

Doing it this way may lose some image_id numbers along the way. You may want to do this:

SELECT COUNT(1) INTO @image_is_there FROM images
WHERE image_name='someimage.jpg'
AND image_folder='/some/linux/folder';

IF @image_is_there IS 0, then

INSERT INTO images (image_name,image_folder)
VALUES ('someimage.jpg','/some/linux/folder');
Related Question