File name as primary key?

Question

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?

Answer 1

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');

Answer 2

In general, surrogate PKs are preferable compared to natural. Usually, primary key columns never get updated; in your case renaming file will require updating primary key which in turn may cause cascade updates. Aside from that, having a long primary key is never a good idea from storage and performance point of view.

Depends on RDMS you use, primary key may also define how the rows stored (the physical order), so even inserting will cause many more page splits and high level of fragmentation.

Summary .I'd strongly recommend not to use file names as primary keys. If they have to be unique, you always have an option to create unique constraint.

Answer 3

I think your best option is to create a (auto-number) surrogate key (image_id) and use that as your PRIMARY KEY. Of course the how depends on your DBMS. MSSQL and MySQL let you designate auto-numbers, but not all DBMSs do.