0

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.

Improve this question
3
  • 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
    Commented Jan 21, 2012 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
    Commented Jan 21, 2012 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
    Commented Jan 21, 2012 at 10:40

2 Answers 2

Reset to default
2

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.

Improve this answer
-1

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

Improve this answer
2
  • 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
    Commented Jan 21, 2012 at 10:43
  • Does any know of a Random generator in EXCEL with checking for duplicates on insert?
    – Arthor
    Commented Jan 21, 2012 at 11:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.