correct FK PK relationship

Question

I have a new layout to figure it has 3 tables

UnfiledFiles
SortedFiles
FileVersion

The process will start with an UnfiledFile Record and a FileVersion and several versions may be added all joined to the same UnfiledFile
From there I am going to need to tie all the Versions for a particular UnfiledFile record with a SortedFile Record.

What organization would you see this working best with?

Answer 1

Foreign Key in the UnfiledFiles table, that is the Primary Key in the SortedFiles table.

Foreign Key in the UnfiledFiles table, that is the Primary Key in the FileVersion table (assuming that that is also a one-to-many relationship).

Answer 2

Why store UnfiledFiles and SortedFiles as separate tables? It appears that being unfiled versus sorted is just an attribute of a file.

So you need two tables: Files and Versions. Versions contains a foreign key to Files. Files contains an attribute column is_sorted.

CREATE TABLE Files (
  file_id   INT PRIMARY KEY,
  is_sorted INT NOT NULL DEFAULT 0
);

CREATE TABLE Versions (
  file_id    INT NOT NULL,
  version    INT NOT NULL,
  PRIMARY KEY (file_id, version),
  FOREIGN KEY (file_id) REFERENCES Files (file_id)
);

If you want to make it clear which files you're querying, you can create views:

CREATE VIEW UnfiledFiles AS SELECT * FROM Files WHERE is_sorted = 0;

CREATE VIEW SortedFiles AS SELECT * FROM Files WHERE is_sorted <> 0;