I started a project to build my own invoicing and management system to take the place of prohibitively expensive QuickBooks software. This will be broken down in 5 steps, with the current step in bold font, and previous steps linked.
- Design the DB schema and table relationships, and insert data for standards tables
- Create and test procedures and functions
- Design application behavior
- Design user interface
- Design export methods and formats
I got a chance to sit down with a friend who's a really good SQL dev. Here are the changes I made.
- Remove underscores from column names.
- Further normalize my
Person
data, i.e., address phone & email, to account that a person can have multiple of each. Also normalized even more by adding types for each. - Changed
ProjectType
toProduct
as it makes more sense that way. - Added prices and more product information in the
Product
table. - Changed
ProjectDetail
toInvoiceDetail
and tied it toInvoice
andProject
and (obviously) toPerson
andAddress
. - Normalized
InvoicingPayment
withPaymentType
. - Normalized
Invoice
with anInvoiceStatus
table. (thanks @200_success for pointing outBOOLEAN
was a bad idea!) - Changed the order the tables are created so that
FOREIGN KEY
constraints are declared in theCREATE TABLE
statement. - Markdown throughout for clarity.
DROP DATABASE IF EXISTS PsychoProductions;
CREATE DATABASE PsychoProductions;
USE PsychoProductions;
--
-- Create table with standard values
-- to be referenced to by other tables
-- And insert some values in those tables
--
-- Person types
CREATE TABLE PersonType
(
PersonTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (PersonTypeId),
PersonTypeName VARCHAR(30)
)
;
INSERT INTO PersonType
(PersonTypeName)
VALUES
('Staff'),
('Partner'),
('Customer'),
('Vendor'),
('Session musician')
;
-- Billing methods
CREATE TABLE BillingMethod
(
BillingMethodId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (BillingMethodId),
BillingMethod VARCHAR(30)
)
;
INSERT INTO BillingMethod
(BillingMethod)
VALUES
('Unassigned'),
('Net 30'),
('Net 15'),
('Cash on delivery'),
('Cash with order')
;
-- Product types
CREATE TABLE ProductType
(
ProductTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ProductTypeId),
ProductTypeName VARCHAR(150),
ProductTypeCost DECIMAL (6,2),
ProductTypeStandard BOOLEAN, -- Set to False if ad hoc project type
ProductTaxable BOOLEAN DEFAULT False -- No need to tax product if not physical good
)
;
INSERT INTO ProductType
(ProductTypeName, ProductTypeCost, ProductTypeStandard, ProductTaxable)
VALUES
('Basic musical arrangement (3 or fewer)', 30, True, False),
('Basic musical arrangement (4 or more)', 25, True, False),
('Advanced musical arrangement (3 or fewer)', 50, True, False),
('Advanced musical arrangement (4 or more)', 40, True, False),
('Instrumental leasing (3 or fewer)', 25, True, False),
('Instrumental leasing (4 or more)', 20, True, False),
('Instrumental leasing (NAPH 3 or more)', 20, True, False),
('Graphic design (album sleeve)', 80, True, False),
('Graphic design (full CD & sleeve)', 150, True, False),
('Graphic design (full CD, sleeve & booklet)', 200, True, False),
('Graphic design (flyers)', 40, True, False),
('Graphic design (t-shirt)', 30, True, False),
('Graphic design (logo, sticker, small items)', 25, True, False),
('Rush uplift charge (Basic project)', 10, True, False),
('Rush uplift charge (Advanced project)', 20, True, False)
;
-- Invoice Status types
CREATE TABLE InvoiceStatus
(
InvoiceStatusID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (InvoiceStatusId),
InvoiceStatus VARCHAR(30)
)
;
INSERT INTO InvoiceStatus
(InvoiceStatus)
VALUES
('Open'),
('Paid'),
('Partially Paid'),
('Cancelled')
;
-- Transaction types
CREATE TABLE TransactionType
(
TransactionTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (TransactionTypeId),
TransactionType VARCHAR(10)
)
;
INSERT INTO TransactionType
(TransactionType)
VALUES
('Debit'),
('Credit')
;
-- Address types
CREATE TABLE AddressType
(
AddressTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (AddressTypeId),
AddressType VARCHAR(20)
)
;
INSERT INTO AddressType
(AddressType)
VALUES
('Unique'),
('Physical'),
('Shipping'),
('Billing'),
('Mailing')
;
-- Phone types
CREATE TABLE PhoneType
(
PhoneTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (PhoneTypeId),
PhoneType VARCHAR(20)
)
;
INSERT INTO PhoneType
(PhoneType)
VALUES
('Mobile'),
('Business'),
('Home'),
('Fax'),
('Pager')
;
-- Email types
CREATE TABLE EmailType
(
EmailTypeId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (EmailTypeId),
EmailType VARCHAR(20)
)
;
INSERT INTO EmailType
(EmailType)
VALUES
('Business'),
('Personal')
;
--
-- Create master tables which will contain actual business data
--
/* CREATE ALL CORE TABLES RELATED TO PERSONS */
-- This table will contain primary person information
CREATE TABLE Person
(
PersonId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (PersonId),
PersonTypeId INT NOT NULL DEFAULT 3,
FOREIGN KEY (PersonTypeId) REFERENCES PersonType(PersonTypeId),
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40),
Organization VARCHAR (100),
Website VARCHAR(100),
DefaultBillingMethodId INT NOT NULL DEFAULT 1,
FOREIGN KEY (DefaultBillingMethodId) REFERENCES BillingMethod(BillingMethodId),
Active BOOLEAN DEFAULT True,
CreationDate TIMESTAMP DEFAULT NOW()
)
;
-- Addresses here
CREATE TABLE Address
(
AddressId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (AddressId),
PersonId INT NOT NULL, -- One-to-many relationship
FOREIGN KEY (PersonID) REFERENCES Person(PersonID),
AddressTypeId INT NOT NULL DEFAULT 1, -- Unique
FOREIGN KEY (AddressTypeId) REFERENCES AddressType(AddressTypeId),
Address VARCHAR(100),
City VARCHAR(40),
State VARCHAR(2),
ZipCode VARCHAR(5)
)
;
-- Phone numbers here
CREATE TABLE Phone
(
PhoneId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (PhoneId),
PersonId INT NOT NULL, -- One-to-many relationship
FOREIGN KEY (PersonId) REFERENCES Person(PersonId),
PhoneNumber VARCHAR(20) NOT NULL,
PhoneTypeId INT NOT NULL DEFAULT 1, -- Mobile
FOREIGN KEY (PhoneTypeId) REFERENCES PhoneType(PhoneTypeId)
)
;
-- Emails here
CREATE TABLE Email
(
EmailId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (EmailId),
PersonId INT NOT NULL, -- One-to-many relationship
EmailAddress VARCHAR(50) NOT NULL,
FOREIGN KEY (PersonId) REFERENCES Person(PersonId),
EmailTypeId INT NOT NULL DEFAULT 1, -- Business
FOREIGN KEY (EmailTypeId) REFERENCES EmailType(EmailTypeId)
)
;
/* CREATE ALL TABLES RELATED TO PROJECTS */
-- This table will contain primary project information
CREATE TABLE Project
(
ProjectId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (ProjectId),
RequestPersonId INT NOT NULL,
FOREIGN KEY (RequestPersonID)
REFERENCES Person(PersonID),
AssignPersonId INT,
FOREIGN KEY (AssignPersonID)
REFERENCES Person(PersonID),
ProjectName VARCHAR(200) NOT NULL,
Description TEXT,
OrderDate DATE NOT NULL,
DueDate DATE,
CompleteDate DATE
)
;
-- Line number of product in project (tied to transactions)
/* CREATE ALL TABLES RELATED TO MONEY */
-- Invoices here
CREATE TABLE Invoice
(
InvoiceId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (InvoiceId),
ProjectId INT NULL, -- Not all invoices will be tied to a project
FOREIGN KEY (ProjectId) REFERENCES Project (ProjectId),
InvoiceByPersonId INT NOT NULL,
FOREIGN KEY (InvoiceByPersonId) REFERENCES Person(PersonId),
BillToPersonId INT NOT NULL,
FOREIGN KEY (BillToPersonId) REFERENCES Person(PersonId),
BillToAddressId INT NOT NULL,
FOREIGN KEY (BillToAddressId) REFERENCES Address(AddressId),
ShipToAddressId INT NULL, -- Most invoiced products are not physical products
FOREIGN KEY (ShipToAddressId) REFERENCES Address(AddressId),
InvoiceStatusId INT NOT NULL DEFAULT 1, -- Open
FOREIGN KEY (InvoiceStatusId) REFERENCES InvoiceStatus (InvoiceStatusId),
InvoiceDate DATE NOT NULL,
InvoicePaidDate DATE NULL
)
;
CREATE TABLE InvoiceDetail
(
/*
InvoiceDetailId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (InvoiceDetailId),
*/
InvoiceId INT NOT NULL,
FOREIGN KEY (InvoiceId) REFERENCES Invoice(InvoiceId),
InvoiceSequenceId INT NOT NULL,
ProductTypeId INT NOT NULL,
FOREIGN KEY (ProductTypeId) REFERENCES ProductType(ProductTypeId),
Quantity INT NOT NULL DEFAULT 1,
TaxableRate DECIMAL(5,2)
)
;
-- Monetary transactions will be logged here
CREATE TABLE AccountingTransaction
(
TransactionId INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (TransactionId),
TransactionTypeId INT NOT NULL,
FOREIGN KEY (TransactionTypeId) REFERENCES TransactionType(TransactionTypeId),
ProjectId INT NULL, -- Not all transactions will be tied to a project
FOREIGN KEY (ProjectId) REFERENCES Project (ProjectId),
InvoiceId INT NULL, -- Ditto for invoice
FOREIGN KEY (InvoiceId) REFERENCES Invoice(InvoiceId),
InvoiceSequenceId INT NULL, -- Ditto
PaidByPersonId INT NOT NULL,
FOREIGN KEY (PaidByPersonId) REFERENCES Person(PersonId),
PaidToPersonId INT NOT NULL,
FOREIGN KEY (PaidToPersonId) REFERENCES Person(PersonId),
TransactionDate DATE NOT NULL,
TransactionNote VARCHAR(1000)
)
;