Bob bee Because TShirts are serious business

#Bob Bee

The Customer's table, records identities in one place because a single customer could conceivably place many orders over time

Note that US locality is assumed for state,zips, phone etc

CREATE TABLE IF NOT EXISTS Customers(
  CustomerId int AUTO_INCREMENT,
	FirstName VARCHAR(100) NOT NULL,
	LastName VARCHAR(100) NOT NULL,
	Address VARCHAR(100) NOT NULL,
	City VARCHAR(100) NOT NULL,
	State VARCHAR(2) NOT NULL,
	Zip VARCHAR(9) NOT NULL,
	Phone VARCHAR(10) NOT NULL,
	PRIMARY KEY (CustomerId)
);

Since an order can have any number of line items, we will have one table for orders that relates back to customers and another table for invoice lines. We can aggregate the two at query time.

Bob Bee wants to attach certain things arbitarily to individual orders, such as the discount. Using line items lets him define the item structure any way he wants. This could be integrated into an application using case classes that turn on item type

Items to orders relation is n to 1

CREATE TABLE IF NOT EXISTS Orders (
	OrderId int AUTO_INCREMENT,
	CustomerId int NOT NULL,
	ProjectName TEXT DEFAULT NULL,
	OrderDate TIMESTAMP,
	DateApproved TIMESTAMP DEFAULT 0,
	PrintDate TIMESTAMP DEFAULT 0,
	Discount INT DEFAULT 0,
	TotalPrice FLOAT DEFAULT 0.0,
	PRIMARY KEY (OrderId),
	CONSTRAINT OrdersCustomer FOREIGN KEY (CustomerID)
	REFERENCES Customers(CustomerId)
);

Bob needs to be able to bill for any service, so we do not want hard coupling between item-dependent traits such as color or location and the representation of an invoice line in our DB. Corner-cases such as price fluctuations on certain colors of ink can be expressed in the generalest sense in the items table. We could expand this to reference another table with item specific traits.

CREATE TABLE IF NOT EXISTS InvoiceLines (
	LineItemId int AUTO_INCREMENT,
	OrderId int NOT NULL,
	ItemName TEXT NOT NULL, /* This is where we can generalize item orders (names like "print pass, left shoulder" or "100 red shirts" */
	ItemDescription TEXT DEFAULT NULL,
	UnitInventoryCost FLOAT DEFAULT 0.0, /* This is where we could record costs for individual items. Service cost is 0, since that is billed via work log */
	Quantity INT DEFAULT 1,
	UnitPrice FLOAT DEFAULT 0.0, 
	LineTotal FLOAT DEFAULT 0.0,
	PRIMARY KEY (LineItemID),
	CONSTRAINT InvoiceLines FOREIGN KEY (OrderId)
	REFERENCES Orders(OrderId)
);

Now for our work log:

CREATE TABLE IF NOT EXISTS Employees (
	EmployeeId int AUTO_INCREMENT,
	FirstName VARCHAR(100) NOT NULL,
	LastName VARCHAR(100) NOT NULL,
	Address VARCHAR(100) NOT NULL,
	City VARCHAR(100) NOT NULL,
	State VARCHAR(2) NOT NULL,
	Zip VARCHAR(9) NOT NULL,
	Phone VARCHAR(10) NOT NULL,
	DateStarted TIMESTAMP NOT NULL,
	PayByHour FLOAT DEFAULT 17.50,
	IsLazy BOOL DEFAULT True,
	PRIMARY KEY (EmployeeId)
);

Hours they spend on individual tasks on items

Note the use of an integer for task type, this allows us to use a normalized list of tasks, but preserves the ability to expand the list in the future on the application side.

CREATE TABLE IF NOT EXISTS WorkLog (
	EmployeeId int NOT NULL,
	LineItemId int NOT NULL,
	DateExecuted TIMESTAMP NOT NULL,
	Task TEXT NOT NULL, 
	TaskType INT DEFAULT 0, 
	StartTime TIMESTAMP,
	FinishedTime TIMESTAMP DEFAULT 0,
	FOREIGN KEY (EmployeeId) REFERENCES Employees(EmployeeId),
	FOREIGN KEY (LineItemId) REFERENCES InvoiceLines(LineItemId)
);

DeaconDesperado/schema.md