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

So here is how I built the schema. Let me know what you think. Any advice on better schema design, etc. is very welcome!

Step 1.a - Build the schema.

CREATE SCHEMA IF NOT EXISTS PsychoProductions;
USE PsychoProductions;
-- Create a bunch of tables
CREATE TABLE Person
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Person_Type_ID INT NOT NULL,
    First_Name VARCHAR(20) NOT NULL,
    Last_name VARCHAR(40),
    Address VARCHAR(100),
    City VARCHAR(40),
    State VARCHAR(2),
    Zip VARCHAR(5),
    Phone1 INT,
    Phone2 INT,
    Email VARCHAR(100),
    Website VARCHAR(100),
    Default_Billing_Method_ID INT NOT NULL,
    Active BOOLEAN DEFAULT 1
    );
CREATE TABLE Person_Type
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Name VARCHAR(30)
    );
CREATE TABLE Billing_Method
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Name VARCHAR(30)
    );
CREATE TABLE Project
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Request_Person_ID INT NOT NULL,        
    Assigned_Person_ID INT,        
    Title VARCHAR(100),
    Description TEXT,
    Order_Date DATETIME NOT NULL,
    Due_Date DATETIME,
    Complete_Date DATETIME
    );
CREATE TABLE Project_Detail
    (
    Project_ID INT NOT NULL,
    Line_ID INT NOT NULL,
    Project_Type_ID INT NOT NULL
    );
CREATE TABLE Project_Type
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Name TEXT
    );
CREATE TABLE Project_Type_Cost
    (
    Project_Type_ID INT NOT NULL,
    Cost DECIMAL (8,2)
    );
CREATE TABLE Invoice
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Project_ID INT NOT NULL,
    Default_Billing_Method BOOLEAN DEFAULT 1,
    Other_Billing_Method INT,
    Paid BOOLEAN DEFAULT 0,
    Paid_Date DATETIME
    );
CREATE TABLE Payment
    (
    ID INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (ID),
    Invoice_ID INT,
    Person_Pay_ID INT,
    Person_Receive_ID INT,
    Date_Paid DATETIME
    );
-- Add constraints now that all tables exist
ALTER TABLE Person
    ADD FOREIGN KEY (Person_Type_ID) REFERENCES Person_Type(ID),
    ADD FOREIGN KEY (Default_Billing_Method_ID) REFERENCES Billing_Method(ID)
    ;
ALTER TABLE Project
    ADD FOREIGN KEY (Request_Person_ID) REFERENCES Person(ID),
    ADD FOREIGN KEY (Assigned_Person_ID) REFERENCES Person(ID)
    ;
ALTER TABLE Project_Detail
    ADD FOREIGN KEY (Project_ID) REFERENCES Project(ID),
    ADD FOREIGN KEY (Project_Type_ID) REFERENCES Project_Type(ID)
    ;
ALTER TABLE Project_Type_Cost
    ADD FOREIGN KEY (Project_Type_ID) REFERENCES Project_Type(ID)
    ;
ALTER TABLE Invoice
    ADD FOREIGN KEY (Project_ID) REFERENCES Project(ID),
    ADD FOREIGN KEY (Other_Billing_Method) REFERENCES Billing_method(ID)
    ;
ALTER TABLE Payment
    ADD FOREIGN KEY (Person_Pay_ID) REFERENCES Person(ID),
    ADD FOREIGN KEY (Person_Receive_ID) REFERENCES Person(ID)
    ;

Step 1.b - Populate tables which are designed as standard FOREIGN KEYs

USE PsychoProductions;
INSERT INTO Person_Type 
    (Name)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;
INSERT INTO Billing_Method
    (Name)
VALUES
    ('Net 30'),
    ('Net 15'),
    ('Cash on delivery'),
    ('Cash on order'),
    ('Unassigned')
;
INSERT INTO Project_Type
    (ID, Name)
VALUES
    (1, 'Basic musical arrangement (3 or fewer)'), 
    (2, 'Basic musical arrangement (4 or more)'), 
    (3, 'Advanced musical arrangement (3 or fewer)'), 
    (4, 'Advanced musical arrangement (4 or more)'), 
    (5, 'Instrumental leasing (3 or fewer)'), 
    (6, 'Instrumental leasing (4 or more)'), 
    (7, 'Instrumental leasing (NAPH 3 or more)'), 
    (8, 'Graphic design (album sleeve)'), 
    (9, 'Graphic design (full CD & sleeve)'), 
    (10, 'Graphic design (full CD, sleeve & booklet)'), 
    (11, 'Graphic design (flyers)'), 
    (12, 'Graphic design (t-shirt)'), 
    (13, 'Graphic design (logo, sticker, small items)') 
;
INSERT INTO Project_Type_Cost
    (Project_Type_ID, Cost)
VALUES
    (1, 30),
    (2, 25),
    (3, 50),
    (4, 40),
    (5, 25),
    (6, 20),
    (7, 20),
    (8, 80),
    (9, 150),
    (10, 200),
    (11, 40),
    (12, 30),
    (13, 25)
;

I'm currently using MySQL with plans to likely port to MariaDB soon.

Answer 1

I'll review your SQL statements as code. For a proper review of the schema, consult Database Administrators instead.

You haven't declared any foreign key constraints. You should do so, as such constraints act as a sanity check for the correctness of your data. For an accounting application, the performance impact would be a non-issue, while the benefits for data integrity would be significant. With MySQL, I believe you'll need to use InnoDB tables to get the benefit of foreign key constraints.

With foreign key constraints, you'll find that certain tables are more fundamental than others. For example, the Person_Type table would need to be defined before Person, since Person has a FOREIGN KEY (Person_Type_ID) REFERENCES Person_Type (ID) constraint.

Phone numbers should not be of type INT. For one, an INT only stores numbers up to 2³¹-1, which is a bit more than 9 decimal digits. (The tenth digit must be ≤ 2!) That's not even enough to store a North American 10-digit phone number reliably. Another reason is that phone numbers may contain significant leading zeros (particularly European ones). They could also have notes such as an extension number, or punctuation such as * or #.

The DATETIME type is rarely useful, I think. DATETIME represents a date and time, but in a "floating" time zone, so it always needs additional information to be interpreted. Prefer either DATE (if you need just a date) or TIMESTAMP (if the time matters).

I find it awkward that Project_Type_Cost is a separate table rather than just a column of Project_Type.

In the Invoice table, I don't understand the purpose of Default_Billing_Method. Representing Paid as a BOOLEAN is sketchy from an accounting perspective: what if the payment amount is not exactly the amount of the invoice? A proper accounting application should track the credits and debits for each account, I think.