opc.three (4/4/2012)

---

The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb

GO

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'dbo.FileDetail')

AND type IN (N'U') )

DROP TABLE dbo.FileDetail;

GO

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'dbo.FileMaster')

AND type IN (N'U') )

DROP TABLE dbo.FileMaster;

GO

CREATE TABLE dbo.FileMaster

(

FileID INT NOT NULL

IDENTITY(1, 1),

FilePathInitial NVARCHAR(260) NOT NULL,

CreateDate DATETIME NOT NULL

CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),

CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)

);

GO

CREATE TABLE dbo.FileDetail

(

FileDetailID INT NOT NULL

IDENTITY(1, 1),

FileID INT NOT NULL,

OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.

FilePathOriginal NVARCHAR(260) NOT NULL,

FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D

CreateDate DATETIME NOT NULL

CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),

CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),

CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)

);

GO

DECLARE @FileID INT;

INSERT INTO dbo.FileMaster

(

FilePathInitial

)

VALUES (

N'\\server\drop\files\someFile.pgp'

);

SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail

(

FileID,

OperationType,

FilePathOriginal,

FilePathFinal

)

VALUES (

@FileID,

'MOV',

N'\\server\drop\files\someFile.pgp',

N'\\server\drop\files\someFile_20120404_100322.pgp'

);

INSERT INTO dbo.FileDetail

(

FileID,

OperationType,

FilePathOriginal,

FilePathFinal

)

VALUES (

@FileID,

'DEC',

N'\\server\drop\files\someFile_20120404_100322.pgp',

N'\\server\drop\files\someFile_20120404_100322.txt'

);

INSERT INTO dbo.FileDetail

(

FileID,

OperationType,

FilePathOriginal,

FilePathFinal

)

VALUES (

@FileID,

'DEL',

N'\\server\drop\files\someFile_20120404_100322.txt',

NULL

);

-- show a file's lifespan

SELECT fd.FileDetailID,

fd.FileID,

fd.OperationType,

fd.FilePathOriginal,

fd.FilePathFinal,

fd.CreateDate

FROM dbo.FileMaster fm

JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID

WHERE fm.FileID = 1

ORDER BY fd.FileDetailID;

GO