0

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
);
GO

How 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.

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by pritaeas
0

An int so you can store the UserID would be a better choice in my opinion. Then you can link the user information in your queries.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.