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 will have to do this process in EXCEL first before it is made into a database. I will have many files, but there is about 7 - 9 files that work all together as one e.g.

X192AD1-test.docx
X192AD1-test.avi
X192AD1-test.mpg
X192AD1-test-small.jpg
X192AD1-test-preview.jpg
X192AD1-test-pdf

All the above works as ONE file.

I do not want to use a numbering system, e.g. 00001, 00002, 00003 because people can determine the age of the files, 00001 is older than 000032.

Can anyone give me some advice regarding this because of course I cannot repeat the primary key and I am not sure how to approach this.

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 12:34

This question came from our site for professional and enthusiast programmers.

1  
Whats the problem with people determining the age of the files? It looks like youre complicating things unnessesarily by eliminating this simple solution... –  ain Jan 21 '12 at 10:33
    
What is the problem with using a number? You can always hide it from view in the application using the database. –  Gigi Jan 21 '12 at 10:34
    
I cannot as the name of the files is also used as the reference to do a key search to locate the content. The age is important as some files will be used across different clients. but from experience I have had the question. We have 0001 and 0003 but where is 0002 please. Of course that content is not for the client. Hence with a random system, this will not happen. The complication is necessary as it is the same problem I have faced in the past. Thanks –  Arthor Jan 21 '12 at 10:40

2 Answers 2

Create a UUID for each entry, checking for duplicates on insert.

share|improve this answer
    
So a universally unique identifier (UUID) could be the way to got. Of course I will have to make this in excel fist then move it all over to the DB. –  Arthor Jan 21 '12 at 10:43
    
Does any know of a Random generator in EXCEL with checking for duplicates on insert? –  Arthor Jan 21 '12 at 11:32

You need 2 tables: one called say, Fileset and the other FilesetFile

  • Fileset contains information about the group: source, owner, who and when etc
  • FilesetFile contains a foreign key to Fileset.

Example:

Fileset

FilesetID, PK
FilesetOwner
FilesetAddedBy
...

FilesetFile

FilesetID, PK, FK
FileSuffix, PK
FileContent (BLOB column say)
...

You can then create a computed column "FileName" that concatenates FilesetID and FileSuffix to generate the pattern you need above

Trying this with one table will end in failure.

share|improve this answer

Your Answer

 
discard

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