0

In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables:

┌──────────┐ ┌──────────────────┐ ┌────────┐
│projects  │ │downloads         │ │files   │
├──────────┤ ├──────────────────┤ ├────────┤
│project_id│ │download_id       │ │file_id │
│name      │ │project_id        │ │contents│
│(FK?)     │ │download_timestamp│ │(FK?)   │
└──────────┘ │file_id           │ └────────┘
             │(FK?)             │
             └──────────────────┘
  • All downloads and files belong to a project.
  • Everytime a file is downloaded, a new download is inserted.
  • If the same contents already exists in the files table for the same project, the new download will point to the existing file, otherwise a new file is also inserted.

I have two foreign keys in downloads, project_id and file_id. Is there a better structure, especially one that prevents orphaned files (files without an associated download)?

-1

Schematically (out of syntax).

Structures:

CREATE TABLE project ( project_id identity,
                       primary key (project_id),
                       name varchar,
                       unique (name) );

CREATE TABLE file ( file_id identity,
                    primary key (file_id),
                    filelink varchar,
                    content varchar,
                    unique (bf(file_link || content)) );

CREATE TABLE download ( download_id identity,
                        primary key (download_id),
                        project_id,
                        foreign key (project_id) references project (project_id),
                        file_id,
                        foreign key (file_id) references file (file_id),
                        download_timestamp default current_timestamp );

Insertion:

INSERT INTO project (name)
VALUES (@name)
ON CONFLICT (name) DO NOTHING;

INSERT INTO file (filelink, content)
VALUES (@filelink, @content)
ON CONFLICT (filelink, content) DO NOTHING;

INSERT INTO downloads (project_id, file_id)
SELECT project.project_id, file.file_id
FROM project, file
WHERE project.name = @name
  AND file.filelink = @filelink
  AND file.content = @content;
  • 1
    How is this different from my structure? It still allows files without an associated download. – AndreKR May 21 at 14:17
  • @AndreKR 1) It is NOT a problem at all... 2) You cannot provide both direct and backward reference integrity (forbid both file without download and download without file), because if you try to do it, you can't insert data - any insert will fail because no referenced record. To solve it you'll need one more table. 3) And one more reason - if you'll delete some project and its downloads, the files remains on HDD, and it makes no sense to delete information about them. Let's will unbound... – Akina May 21 at 18:09
  • There are no files on HDD, everything is in the database. I edited the question to make that more clear. By "orphaned file" I mean a files entry that no downloads entry points to. And of course I would make all the foreign keys deferrable. – AndreKR May 21 at 20:04
  • @AndreKR By "orphaned file" I mean a files entry that no downloads entry points to. To avoid these files to appear while delete referenced download set ON DELETE CASCADE action for FK. To avoid these files to appear while update forbid this action - the scheme do not provide updates. You cannot avoid these files to appear while insert - they are produced in the above procedure between 2nd and 3rd query, so they are legitimate. You must 1) check the whole procedure is performed without errors (wrap it into transaction) 2) prevent insertion performed not by this procedure. – Akina May 22 at 5:02
  • "To avoid these files to appear while delete referenced download set ON DELETE CASCADE action for FK." What? In your proposal downloads references files. Deleting downloads should do nothing about files, even with ON DELETE CASCADE. – AndreKR May 22 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.