Revision 1 - Step 1: PsychoProductions management tool project

Question

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.

1. Design the DB schema and table relationships, and insert data for standards tables
2. Create and test procedures and functions
3. Design application behavior
4. Design user interface
5. Design export methods and formats

Here is the original question

I got a chance to sit down with a friend who's a really good SQL dev. Here are the changes I made.

1. Remove underscores from column names.
   
2. 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.
   
3. Changed ProjectType to Product as it makes more sense that way.
4. Added prices and more product information in the Product table.
5. Changed ProjectDetail to InvoiceDetail and tied it to Invoice and Project and (obviously) to Person and Address.
6. Normalized InvoicingPayment with PaymentType.
7. Normalized Invoice with an InvoiceStatus table. (thanks @200_success for pointing out BOOLEAN was a bad idea!)
8. Changed the order the tables are created so that FOREIGN KEY constraints are declared in the CREATE TABLE statement.
9. 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)
    )
;

Answer 1

Normalization

Database schemas should typically be normalized, and, it appears that this schema is reasonably well structured. I don't see any massive normalization problems, but I see some issues:

1. Time-sequence data - your Product table contains the Product cost value. Are your products going to be the same cost forever? If the cost changes, it will screw up your invoice data.....
2. Address has a PersonID attached to it. This is not completely unusual, but, when you have Invoice, you have both a BillToPersonId, and a BillToAddressId (and the address has a person), so what if they are different? Is the PersonId on the Address redundant? I think there's a contradiction in there somewhere....

Dates

For audit/tracking purposes, you should normally put a CreatedTimestamp on every record. You have one on the Person, but nothing else. The Timestamp should be a DateTime value.

Many of your current Date-based columns should also contain a Time component, not just a Date.

Person

You have the table Person, but, what if the person is a business or something? This seems very specific to me, not being able to do transactions with non-person entities...

Column Types

I feel that your column types are all rather small, and conservative.

• FirstName and LastName are limited to 40 characters. That's short

• EMailAddress is limited to 50 chars?

• Address is 100 chars?

• City is 40 chars (Poor Tweebuffelsmeteenskootmorsdoodgeskietfontein)?

• other columns should be extended too.

Answer 2

• ProductType should be normalized a bit further with a ProductCategory table. Alternatively, you could add a description column to product type, but I think the category table makes more sense.

• Instead of Taxable being a Boolean, you may want to consider using a TaxCode table instead. It depends on how robust you want this to be. In retail it would certainly be a TaxCode, but it could be fine for your limited purposes.

• You don't take cash payments? (Hint: Even if you don't, it should be there. Sooner or later you'll need to input a cash payment.)