Advice on choosing a primary key

Posted on

Question :

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.

Answer :

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *