UDB.sql: User Database Schema

CREATE TABLE Users
(
	ID SMALLINT NOT NULL,
	UserName VARCHAR(15) NOT NULL,
	RealName VARCHAR(40) NULL,
	PhoneNumber VARCHAR(15) NULL,
	BillingPlan TINYINT NULL,
	AccountBalance  SMALLMONEY NOT NULL DEFAULT(0.00),
	Hours SMALLINT NULL DEFAULT(0),

	CONSTRAINT PK_ID_Users PRIMARY KEY CLUSTERED (ID),
	CONSTRAINT UQ_UserName_Users UNIQUE NONCLUSTERED (UserName),
	CONSTRAINT FK_BillingPlan_Users FOREIGN KEY (BillingPlan)
		REFERENCES BillingPlans(ID)
)

CREATE TABLE BillingPlans
(
	ID TINYINT NOT NULL,
	Name VARCHAR(20) NULL,
	BaseRate SMALLMONEY NOT NULL DEFAULT(0.00),
	BaseHours TINYINT NOT NULL DEFAULT(0),
	HourlyRate SMALLMONEY NOT NULL DEFAULT(0.00),

	CONSTRAINT PK_ID_BillingPlans PRIMARY KEY CLUSTERED (ID)
)

CREATE TABLE History
(
	ID NUMERIC(10,0) IDENTITY,
	UID SMALLINT NOT NULL,
	Date DATETIME NOT NULL,
	Transaction VARCHAR(40) NOT NULL,
	Amount SMALLMONEY NOT NULL,

	CONSTRAINT PK_ID_History PRIMARY KEY CLUSTERED (ID),
	CONSTRAINT FK_UID_History FOREIGN KEY (UID)
		REFERENCES Users(ID)
)