Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What will you do when you have (or someone tries to upload) multiple files that happen to have the same name? How does that affect the users of this website? –  Nick Chammas Dec 22 '11 at 19:21
    
Users have no ability to upload content. I'll associate images, literature and videos with products and the product page will be generated based on the associations between product / media. –  payling Dec 22 '11 at 19:25
add comment

3 Answers

up vote 3 down vote accepted

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');
share|improve this answer
    
Thanks Rolando, good idea. –  payling Dec 22 '11 at 20:03
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.