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.
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.
FilesetID, PK FilesetOwner FilesetAddedBy ...
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.