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 tablesCreate and test procedures and functionsDesign application behaviorDesign user interfaceDesign export methods and formatsHere is the original questionI 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 to Product as it makes more sense that way. Added prices and more product information in the Product table.Changed ProjectDetail to InvoiceDetail and tied it to Invoice and Project and (obviously) to Person and Address.Normalized InvoicingPayment with PaymentType. Normalized Invoice with an InvoiceStatus table. (thanks @200_success for pointing out BOOLEAN was a bad idea!)Changed the order the tables are created so that FOREIGN KEY constraints are declared in the CREATE 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 typesCREATE 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 methodsCREATE 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 typesCREATE 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 typesCREATE 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 typesCREATE TABLE TransactionType    (    TransactionTypeId INT NOT NULL AUTO_INCREMENT,        PRIMARY KEY (TransactionTypeId),    TransactionType VARCHAR(10)    );INSERT INTO TransactionType    (TransactionType)VALUES    ('Debit'),    ('Credit');-- Address typesCREATE 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 typesCREATE 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 typesCREATE 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 informationCREATE 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 hereCREATE 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 hereCREATE 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 hereCREATE 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 informationCREATE 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 hereCREATE 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 hereCREATE 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)    );
Revision 1 - Step 1: PsychoProductions management tool project
sql;mysql
NormalizationDatabase 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: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.....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....DatesFor 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.PersonYou 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 TypesI feel that your column types are all rather small, and conservative.FirstName and LastName are limited to 40 characters. That's shortEMailAddress is limited to 50 chars?Address is 100 chars?City is 40 chars (Poor Tweebuffelsmeteenskootmorsdoodgeskietfontein)?other columns should be extended too.