April 4, 2012 at 9:30 pm
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
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 4, 2012 at 9:32 pm
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
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 4, 2012 at 9:33 pm
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
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
April 4, 2012 at 9:33 pm
Opc.three and Craig thank you very much for you advice and efforts in sharing your knowledge. I do very much value your replies. Thanks.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy