Step 1: PsychoProductions management tool project

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

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_I

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 231-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.

StackExchange Code Review Q#55583, answer score: 6