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 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

CREATE TABLE IF NOT EXISTS `files` (
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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 backend,

<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 orignal 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.

thanks.

share|improve this question
    
nevermind 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 –  user1141688 Feb 13 '13 at 13:17
    
Write that up as an answer, and accept it. You can accept your own answers, and it will show that this question has an answer. (A comment won't show this as an answered question.) –  Mike Sherrill 'Cat Recall' Feb 13 '13 at 14:19

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.