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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that stores additional data pertaining to files/images saved in files elsewhere. Each entry can be associated with another type in the system.

For users there may be one or more entries depending on how many files have been uploaded.

For depots, deliveries the same applies, a stripped down version of the table looks like this:

  `file_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '...',
  `file_company_id` int(11) NOT NULL DEFAULT '0' COMMENT '...',
  `file_relation` varchar(100) NOT NULL COMMENT '...',
  `file_relation_id` int(11) NOT NULL COMMENT '...',
  `file_name` varchar(200) NOT NULL COMMENT '...',      
  `file_mime_type` varchar(150) NOT NULL COMMENT '...',
  PRIMARY KEY (`file_id`),
  KEY `file_relation` (`file_relation`),
  KEY `file_relation_id (`file_relation_id`),
  KEY `file_name` (`file_name`),
  KEY `file_mime_type` (`file_mime_type`),
  KEY `file_company_id` (`file_company_id`)

To get an individual entry is simple enough to query the file_id, to get a list of all entries for another entry I do something like the following:

User 123
SELECT .... FROM files WHERE file_relation='user' AND file_relation_id=123 

When users upload images they are displayed in the UI either in their original form, or a variety of sizes which are registered in the back end:

<img src="path/to/image/1/200" style="width:200px;" />

The above snippet requests image 1 with a width of 200px ... if the size does not exist yet it is created on the fly (if it is a registered size).

The problem I now have is ensuring that only one entry for the new size is created when first inserting.

#when creating new sizes they reference the original file
file_id:99, file_relation:'files',file_relation_id:1,file_dimension:200

#for another file - this is valid
file_id:100, file_relation:'files',file_relation_id:5,file_dimension:200

#this entry should not have been created because a previous one with the dimension exists
file_id:101, file_relation:'files',file_relation_id:1,file_dimension:200

There can be duplicate file_dimension entries for different parent relations, but if another thread is inserting at the same time it should be able to pick up that an entry already exists and not create a new one.

I am not sure how row locking would work for this scenario (when INSERT is used) or if I even need to worry about it, here is what happens currently:

SELECT * FROM `files` WHERE `file_relation`='files AND `file_relation_id`=1 AND `file_dimension`=200
#if no entry is found
INSERT INTO FILES (...,`file_dimension`) VALUES (...,200)

I am quite certain that there are other places in my application where this type of thing also applies, any advice appreciated.

share|improve this question

The OP answered their own question in the comments (now deleted):

Never mind I figured it out, using a unique index combo, ALTER TABLE files ADD UNIQUE INDEX (file_relation, file_relation_id, file_dimension) does the trick.

share|improve this answer

Your Answer


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.