Revision 2 - Step 1: PsychoProductions management tool project

This is another revision of my Psycho Productions database, this time refactored from MySQL to PostgreSQL, after I found out about some of the shortcomings of MySQL.

The following is a (simplified) version of relationships in a flowchart. The way I used arrows is: where the arrow originates = FK and where it points to = PK. Only one relationship is optional (the dashed arrow) as a project is allowed to not be invoiced (e.g., if it is declined) and an invoice is allowed not to be a project (e.g., a bill or something like that).

If you wish to see the YUML diagram, it is here.

PS: PostgreSQL automatically creates indexes on PKs, hence not explicitly defining indexes. If you feel other indexes are needed, I'm open to suggestions!

Graphical illustration:

Following is the full schema DDL code. I would appreciate if you could please provide any advice on design or any other aspect.

```
DROP SCHEMA IF EXISTS PsychoProductions;
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
--

-- Person types
CREATE TABLE PersonRole(
PersonRoleId SERIAL PRIMARY KEY,
PersonRoleName TEXT
);
INSERT INTO PersonRole
(PersonRoleName)
VALUES
('Staff'),
('Partner'),
('Customer'),
('Vendor'),
('Session musician')
;
-- Billing methods
CREATE TABLE BillingMethod(
BillingMethodId SERIAL PRIMARY KEY,
BillingMethod TEXT
);
INSERT INTO BillingMethod
(BillingMethod)
VALUES
('Unassigned'),
('Net 30'),
('Net 15'),
('Cash on delivery'),
('Cash with order')
;
-- Product types
CREATE TABLE Product(
ProductId SERIAL PRIMARY KEY,
ProductName TEXT,
ProductCost DECIMAL (8,2),
ProductStandard BOOLEAN DEFAULT True, -- Set to False if ad hoc project type
ProductTaxable BOOLEAN DEFAULT False -- No tax product if not physical good
);
INSERT INTO Product
(ProductName, ProductCost,

I disagree with the recommendation to use an ENUM like 200_success suggests. Your first choice should always be to use a reference table (like the OP currently has). ENUM types are dangerous to modify (see: http://postgresql.1045698.n5.nabble.com/Problems-with-ENUM-type-manipulation-in-9-1-td4844778.html) and the information ends up stored in a table anyway.

Instead, you should be dropping the use of serial columns as your PRIMARY KEY in cases where you have a guaranteed unique KEY. Your PersonRole table becomes this:

CREATE TABLE PersonRole(
    -- look, no serial
    PersonRoleName TEXT PRIMARY KEY
);
INSERT INTO PersonRole 
    (PersonRoleName)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;

And your Person table's FOREIGN KEY changes like so:

CREATE TABLE Person(
    PersonId SERIAL PRIMARY KEY,
    PersonRoleId TEXT NOT NULL DEFAULT 'Customer' 
        REFERENCES BillingMethod(PersonRoleName) -- right here
    REFERENCES PersonRole(PersonRoleId),
    FirstName TEXT NOT NULL,
    LastName TEXT,
    Organization TEXT,
    Website TEXT,
    DefaultBillingMethodId INT NOT NULL DEFAULT 1 
        REFERENCES BillingMethod(BillingMethodId),
    Active BOOLEAN DEFAULT True,
    CreationDate TIMESTAMP DEFAULT NOW()
);

Provided your TEXT columns aren't too big, the difference between using a TEXT and an INTEGER when performing an index lookup is negligible (see: http://www.depesz.com/2012/06/07/123-vs-depesz-what-is-faster/). The same thing can be applied to your BillingMethod and all of your assorted *Type tables.

For your Phone table, I recommend dropping the serial all together and use a compound PRIMARY KEY instead. The serial isn't serving any real purpose, and this will ensure that any given person cannot insert the same phone number more than once (though you could also use UNIQUE(PersonId, PhoneNumber) and get the same effect):

CREATE TABLE Phone(
    PersonId INT NOT NULL -- One-to-many relationship
        REFERENCES Person(PersonId),
    PhoneNumber TEXT NOT NULL,
    PhoneTypeId INT NOT NULL DEFAULT 1 -- Mobile
        REFERENCES PhoneType(PhoneTypeId),
    PRIMARY KEY (PersonId, PhoneNumber) -- right here
);

I must caution against using capitalization for both your table and column naming. PostgreSQL silently turns all of your names to lowercase unless you double quoted the table/column names when you created the tables:

select * from PersonRole;
 personroleid |  personrolename
--------------+------------------
            1 | Staff
            2 | Partner
            3 | Customer
            4 | Vendor
            5 | Session musician

So for clarity purposes, I would recommend using underscores instead (person_name instead of PersonName). Unless, of course, you would prefer to have to double quote all of your identifiers.

Now, this is a purely stylistic change, but I personally feel that your column names (PersonName, ProductType) are overly verbose (I wouldn't want to be the guy stuck writing queries for these tables!).

CREATE TABLE address_type(
    type TEXT PRIMARY KEY
);

CREATE TABLE address(
    id SERIAL PRIMARY KEY,
    person_id INT NOT NULL -- One-to-many relationship
        REFERENCES person(id),
    type TEXT NOT NULL DEFAULT 'Unique'
        REFERENCES address_type(type),
    address TEXT,
    city TEXT,
    state TEXT,
    zipcode TEXT
);

It should be obvious that address.type refers to a type of address, rather than a type of phone number (if it was appropriate to place the phone number type here, then you want to go ahead and prefix it: phone_type). If ambiguities arise from selecting from multiple tables that have the same column name, you can use the table's name to clarify exactly which column you're referring to: address.id and person.id. Comes out to roughly the same amount of typing for worst case scenarios, but saves a fair bit of typing when there's no overlapping names.

If you need to have unique column names (eg. for a VIEW), then you'll need to alias them of course. However, cases like these will be the exception, rather than the norm.

StackExchange Code Review Q#58104, answer score: 8