Hey there,
I was wondering if I could get some guidance on how I could go about handling this problem. I'm creating a simple version control system database. The tables look like the following:
CREATE TABLE Users
(
[UserID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
FirstName VARCHAR(32) NOT NULL,
MiddleInitial CHAR(1),
LastName VARCHAR(32) NOT NULL,
[Location] VARCHAR(48) NOT NULL DEFAULT 'Unknown',
Phone CHAR(14) NOT NULL,
Email VARCHAR(80) NOT NULL
);
GO
CREATE TABLE Documents
(
DocumentID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Filename VARCHAR(256) NOT NULL,
DocumentType VARCHAR(48) NOT NULL,
DateCreated SMALLDATETIME NOT NULL DEFAULT GETDATE(),
DocumentAuthor VARCHAR(68) NOT NULL,
UserID INT NOT NULL --Foreign key from the Users table
);
GO
CREATE TABLE Versions
(
VersionID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DocumentID INT NOT NULL,
CurrentStatus VARCHAR(12) NOT NULL DEFAULT 'Not approved',
DateVersionCreated SMALLDATETIME NOT NULL DEFAULT GETDATE(),
VersionAuthor VARCHAR(68) NOT NULL,
UserID INT NOT NULL --Foreign key from the Users table
);
GOHow can I make it so that the DocumentAuthor/VersionAuthor matches the UserID (the ID of the user who created the document/version of the document) in the Documents/Versions table? Would it be easier if I stored a single "Name" field instead of having "FirstName", "LastName" and "MiddleInitial" fields in the Users table?
Open to other easier options to convey the same information. Appreciate any and all help! Thanks.
