#Final Schema!#

Thanks to all the great reviews on all 3 version of my schema design, I have finally settled on a final design. Here are some things I implemented from this review:

1. Per @cimmanon's suggestion, I renamed all my table and column names to use snake_case, instead of using PascalCase. This will make queries easier to read, while avoiding to use quotation marks on all table and column names if it were PascalCase.

2. Also per @cimmanon's suggestion, I removed the serial INT Id columns from some of the outer table and used the text word as the FK in inner/principal tables. I opted against using ENUM columns in the end, as I have read they can be buggy.

3. I fixed my FK declarations to be consistent throughout, per @200_success recommendation.

4. Also per @200_success, I wrapped the whole thing into a transaction, and used CASCADE while dropping the schema, so start with a clean slate.

5. I also removed comments that were not useful, where the code spoke for itself.

#The code#

START TRANSACTION;
DROP SCHEMA IF EXISTS PsychoProductions CASCADE;
CREATE SCHEMA PsychoProductions;
SET SEARCH_PATH = PsychoProductions;
/*
** Create table with standard values 
** to be referenced to by other tables
** And insert some values in those tables
*/
CREATE TABLE Person_Role(
    Id TEXT UNIQUE
);
INSERT INTO Person_Role 
    (Id)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;

CREATE TABLE Billing_Method(
    Id TEXT UNIQUE
);
INSERT INTO Billing_Method
    (Id)
VALUES
    ('Unassigned'),
    ('Net 30'),
    ('Net 15'),
    ('Cash on delivery'),
    ('Cash with order')
;

CREATE TABLE Product(
    Id SERIAL PRIMARY KEY,
    Name TEXT,
    Cost DECIMAL (8,2),
    Standard BOOLEAN DEFAULT True, -- Set to False if ad hoc project type
    Taxable BOOLEAN DEFAULT False -- No tax if product is not physical good
);
INSERT INTO Product
    (Name, Cost, Standard, Taxable)
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)
;

CREATE TABLE Invoice_Status(
    Id TEXT UNIQUE
);
INSERT INTO Invoice_Status
    (Id)
VALUES
    ('Open'),
    ('Paid'),
    ('Partially Paid'),
    ('Cancelled')
;

CREATE TABLE Transaction_Type(
    Id TEXT UNIQUE
);
INSERT INTO Transaction_Type
    (Id)
VALUES
    ('Debit'),
    ('Credit')
;

CREATE TABLE Address_Type(
    Id TEXT UNIQUE
);
INSERT INTO Address_Type
    (Id)
VALUES
    ('Unique'),
    ('Physical'),
    ('Shipping'),
    ('Billing'),
    ('Mailing')
;

CREATE TABLE Phone_Type(
    Id TEXT UNIQUE
);
INSERT INTO Phone_Type
    (Id)
VALUES
    ('Mobile'),
    ('Business'),
    ('Home'),
    ('Fax'),
    ('Pager')
;

CREATE TABLE Email_Type(
    Id TEXT UNIQUE
);
INSERT INTO Email_Type
    (Id)
VALUES
    ('Business'),
    ('Personal')
;

/* 
** Create master tables which will contain actual business data
*/ 

/* CREATE ALL CORE TABLES RELATED TO PERSONS */

CREATE TABLE Person(
    Id SERIAL PRIMARY KEY,
    Role_Id TEXT NOT NULL DEFAULT 'Customer'
    REFERENCES Person_Role(Id),
    First_Name TEXT NOT NULL,
    Last_Name TEXT,
    Organization TEXT,
    Website TEXT,
    Default_Billing_Method_Id TEXT NOT NULL DEFAULT 'Net 30'
    REFERENCES Billing_Method(Id),
    Active BOOLEAN DEFAULT True,
    Creation_Date TIMESTAMP DEFAULT NOW()
);

CREATE TABLE Address(
    Id SERIAL PRIMARY KEY,
    Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Address_Type_Id TEXT NOT NULL DEFAULT 'Unique'
        REFERENCES Address_Type(Id),
    Address TEXT,
    City TEXT,
    State TEXT,
    Zip_Code TEXT
);

CREATE TABLE Phone(
    Id SERIAL PRIMARY KEY,
    Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Phone_Number TEXT NOT NULL,
    Phone_Type_Id TEXT NOT NULL DEFAULT 'Mobile'
        REFERENCES Phone_Type(Id)
);

CREATE TABLE  Email(
    Id SERIAL PRIMARY KEY,
    Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Email_Address TEXT NOT NULL,
    Email_Type_Id TEXT NOT NULL DEFAULT 'Business'
        REFERENCES Email_Type(Id)
);

/* CREATE ALL TABLES RELATED TO PROJECTS */

CREATE TABLE Project(
    Id SERIAL PRIMARY KEY,
    Request_Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Assign_PersonId INT
        REFERENCES Person(Id),
    Project_Name TEXT NOT NULL,
    Description TEXT,
    Order_Date DATE NOT NULL,
    Due_Date DATE,
    Complete_Date DATE
);

/* CREATE ALL TABLES RELATED TO MONEY */

CREATE TABLE Invoice(
    Id SERIAL PRIMARY KEY,
    Project_Id INT NULL -- Not all invoices will be tied to a project
        REFERENCES Project (Id),
    Invoice_By_Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Bill_To_Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Bill_To_Address_Id INT NOT NULL
        REFERENCES Address(Id),
    Ship_To_Address_Id INT DEFAULT NULL -- Most invoiced products are not physical products
        REFERENCES Address(Id),
    Invoice_Status_Id TEXT NOT NULL DEFAULT 'Open'
        REFERENCES Invoice_Status (Id),
    Invoice_Date DATE NOT NULL,
    Invoice_Paid_Date DATE NULL
);

CREATE TABLE Invoice_Detail(
    Id SERIAL PRIMARY KEY,
    Invoice_Id INT NOT NULL
        REFERENCES Invoice(Id),
    Invoice_Sequence_Id INT NOT NULL,
    Product_Id INT NOT NULL
        REFERENCES Product(Id),
    Quantity INT NOT NULL,
    Taxable_Rate DECIMAL(4,2) DEFAULT 0
);

CREATE TABLE Accounting_Transaction(
    Id SERIAL PRIMARY KEY,
    Transaction_Type_Id TEXT NOT NULL
        REFERENCES Transaction_Type(Id),
    Project_Id INT NULL -- Not all transactions will be tied to a project
        REFERENCES Project (Id),
    Invoice_Id INT NULL -- Not all transactions will be tied to an invoice
        REFERENCES Invoice(Id),
    Invoice_Sequence_Id INT NULL, -- Not all transactions will be tied to an invoice
    Paid_By_Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Paid_To_Person_Id INT NOT NULL
        REFERENCES Person(Id),
    Transaction_Date DATE NOT NULL,
    Transaction_Note TEXT
);
COMMIT;