Revision 2 - Step 1: PsychoProductions management tool project

Question

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:

enter image description here

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, ProductStandard, 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 types
CREATE TABLE InvoiceStatus(
    InvoiceStatusID SERIAL PRIMARY KEY,
    InvoiceStatus TEXT
);
INSERT INTO InvoiceStatus
    (InvoiceStatus)
VALUES
    ('Open'),
    ('Paid'),
    ('Partially Paid'),
    ('Cancelled')
;
-- Transaction types
CREATE TABLE TransactionType(
    TransactionTypeId SERIAL PRIMARY KEY,
    TransactionType TEXT
);
INSERT INTO TransactionType
    (TransactionType)
VALUES
    ('Debit'),
    ('Credit')
;
-- Address types
CREATE TABLE AddressType(
    AddressTypeId SERIAL PRIMARY KEY,
    AddressType TEXT
);
INSERT INTO AddressType
    (AddressType)
VALUES
    ('Unique'),
    ('Physical'),
    ('Shipping'),
    ('Billing'),
    ('Mailing')
;
-- Phone types
CREATE TABLE PhoneType(
    PhoneTypeId SERIAL PRIMARY KEY,
    PhoneType TEXT
);
INSERT INTO PhoneType
    (PhoneType)
VALUES
    ('Mobile'),
    ('Business'),
    ('Home'),
    ('Fax'),
    ('Pager')
;
-- Email types
CREATE TABLE EmailType(
    EmailTypeId SERIAL PRIMARY KEY,
    EmailType TEXT
);
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 information
CREATE TABLE Person(
    PersonId SERIAL PRIMARY KEY,
    PersonRoleId INT NOT NULL DEFAULT 3 
    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()
);
-- Addresses here
CREATE TABLE Address(
    AddressId SERIAL PRIMARY KEY,
    PersonId INT NOT NULL -- One-to-many relationship
        REFERENCES Person(PersonID),
    AddressTypeId INT NOT NULL DEFAULT 1 -- Unique
        REFERENCES AddressType(AddressTypeId),
    Address TEXT,
    City TEXT,
    State TEXT,
    ZipCode TEXT
);
-- Phone numbers here
CREATE TABLE Phone(
    PhoneId SERIAL PRIMARY KEY,
    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)
);
-- Emails here
CREATE TABLE  Email(
    EmailId SERIAL PRIMARY KEY,
    PersonId INT NOT NULL -- One-to-many relationship
            REFERENCES Person(PersonId),
    EmailAddress VARCHAR(50) NOT NULL,
    EmailTypeId INT NOT NULL DEFAULT 1 -- Business
        REFERENCES EmailType(EmailTypeId)
);

/* CREATE ALL TABLES RELATED TO PROJECTS */
-- This table will contain primary project information
CREATE TABLE Project(
    ProjectId SERIAL PRIMARY KEY,
    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 here
CREATE TABLE Invoice(
    InvoiceId SERIAL PRIMARY KEY,
    ProjectId INT NULL -- Not all invoices will be tied to a project
        REFERENCES Project (ProjectId),
    InvoiceByPersonId INT NOT NULL
        REFERENCES Person(PersonId),
    BillToPersonId INT NOT NULL
        REFERENCES Person(PersonId),
    BillToAddressId INT NOT NULL
        REFERENCES Address(AddressId),
    ShipToAddressId INT NULL -- Most invoiced products are not physical products
        REFERENCES Address(AddressId),
    InvoiceStatusId INT NOT NULL DEFAULT 1 -- Open
        REFERENCES InvoiceStatus (InvoiceStatusId),
    InvoiceDate DATE NOT NULL,
    InvoicePaidDate DATE NULL
);
CREATE TABLE InvoiceDetail(
    InvoiceDetailId SERIAL PRIMARY KEY,
    InvoiceId INT NOT NULL
        REFERENCES Invoice(InvoiceId),
    InvoiceSequenceId INT NOT NULL,
    ProductId INT NOT NULL
        REFERENCES Product(ProductId),
    Quantity INT NOT NULL DEFAULT 1,
    TaxableRate DECIMAL(5,2)
);
-- Monetary transactions will be logged here
CREATE TABLE AccountingTransaction(
    TransactionId SERIAL PRIMARY KEY,
    TransactionTypeId INT NOT NULL
        REFERENCES TransactionType(TransactionTypeId),
    ProjectId INT NULL -- Not all transactions will be tied to a project
        REFERENCES Project (ProjectId),
    InvoiceId INT NULL -- Ditto for invoice
        REFERENCES Invoice(InvoiceId),
    InvoiceSequenceId INT NULL, -- Ditto
    PaidByPersonId INT NOT NULL
        REFERENCES Person(PersonId),
    PaidToPersonId INT NOT NULL
        REFERENCES Person(PersonId),
    TransactionDate DATE NOT NULL,
    TransactionNote VARCHAR(1000)
);

Execution:

Query returned successfully with no result in 388 ms.

Anyone interested in the final code, scroll down to my answer from Sept 4. — Phrancis, Commented Sep 5, 2014 at 3:15

cimmanon · Accepted Answer · 2014-07-27 15:46:47Z

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.

200_success · Accepted Answer · 2014-07-26 16:39:48Z

DROP SCHEMA IF EXISTS will fail if there's anything in the schema. Either use DROP SCHEMA IF EXISTS … CASCADE (a bit bold and risky), or don't include it in the script at all.

A you have a lot of SERIAL–TEXT tables with a few fixed values. Those should just be enumerated types in PostgreSQL. Otherwise, you end up with a lot of annoying little joins. It also makes you hard-code assumptions about their ID values, such as InvoiceStatusId INT NOT NULL DEFAULT 1 -- Open. You don't want to have to do this:

=> INSERT INTO Person (
    PersonRoleId, FirstName, LastName, Organization, DefaultBillingMethodId
) VALUES (
    (SELECT PersonRoleId FROM PersonRole WHERE PersonRoleName = 'Vendor'),
    'Phrancis',
    'VG',
    'Psycho',
    (SELECT BillingMethodId FROM BillingMethod WHERE BillingMethod = 'Net 15')
);

An ENUM lets you treat a limited set of strings like as numbers, which is exactly what you want:

=> CREATE TYPE PersonRole AS ENUM ('Staff', 'Partner', 'Customer', 'Vendor', 'Session musician');

-- Perhaps 'Unassigned' should just be represented as NULL?
=> CREATE TYPE BillingMethod AS ENUM ('Net 30', 'Net 15', 'Cash on delivery', 'Cash with order');

=> CREATE TABLE Person(
    PersonId SERIAL PRIMARY KEY,
    PersonRole PersonRole NOT NULL DEFAULT 'Customer',
    FirstName TEXT NOT NULL,
    LastName TEXT,
    Organization TEXT,
    Website TEXT,
    DefaultBillingMethod BillingMethod,
    Active BOOLEAN NOT NULL DEFAULT TRUE,
    CreationDate TIMESTAMP DEFAULT NOW()
);

=> INSERT INTO Person (PersonRole, FirstName, LastName, Organization, DefaultBillingMethod) VALUES
    ('Vendor', 'Phrancis', 'VG', 'Psycho', 'Net 15');

=> SELECT * FROM Person;
-[ RECORD 1 ]--------+---------------------------
personid             | 1
personrole           | Vendor
firstname            | Phrancis
lastname             | VG
organization         | Psycho
website              | 
defaultbillingmethod | Net 15
active               | t
creationdate         | 2014-07-26 09:25:36.844885

=> INSERT INTO Person (FirstName, Lastname, DefaultBillingMethod) VALUES
    ('Illegal', 'Example', 'Blah');
ERROR:  invalid input value for enum billingmethod: "Blah"
LINE 2:         ('Illegal', 'Example', 'Blah');

You declare foreign keys inconsistently (no indent, single indent, double indent, use of the FOREIGN KEY keyword). Personally, I prefer to declare all foreign keys at the end of the table definition, as it declutters the column definitions and allows for multi-column foreign keys:

CREATE TABLE AccountingTransaction(
    TransactionId SERIAL PRIMARY KEY,
    TransactionTypeId INT NOT NULL,
    ProjectId INT NULL, -- Not all transactions will be tied to a project
    InvoiceId INT NULL, -- Ditto for invoice
    InvoiceSequenceId INT NULL, -- Ditto
    PaidByPersonId INT NOT NULL,
    PaidToPersonId INT NOT NULL,
    TransactionDate DATE NOT NULL,
    TransactionNote VARCHAR(1000),
    FOREIGN KEY (TransactionTypeId) REFERENCES TransactionType (TransactionTypeId),
    FOREIGN KEY (ProjectId) REFERENCES Project (ProjectId),
    FOREIGN KEY (InvoiceId) REFERENCES Invoice (InvoiceId),
    FOREIGN KEY (PaidByPersonId) REFERENCES Person (PersonId),
    FOREIGN KEY (PaidToPersonId) REFERENCES Person (PersonId)
);

Taxability depends not just on the product, but the jurisdiction in which the product is sold. A simple taxability column in the Product table might not be flexible enough.

Finally, I'd suggest wrapping the whole thing in a transaction, between BEGIN; and COMMIT; — because PostgreSQL is awesome like that.

What would I gain from creating an enum data type as opposed to using the built-in serial? I'd like to know as this is the first I've heard of enum in SQL. — Phrancis, Commented Jul 26, 2014 at 10:19
There's no benefit to using ENUM in this instance. In fact, I view the suggestion as downright irresponsible, especially if there's a chance the values of the ENUM will change. — cimmanon, Commented Jul 27, 2014 at 15:42

Phrancis · Accepted Answer · 2020-06-10 13:24:26Z

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:

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.
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.
I fixed my FK declarations to be consistent throughout, per @200_success recommendation.
Also per @200_success, I wrapped the whole thing into a transaction, and used CASCADE while dropping the schema, so as to start with a clean slate.
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;

You have tables in there without primary keys. Using text IDs for no apparent reason. Why? — Craig Ringer, Commented Sep 17, 2014 at 3:43
I used text PKs based on the latest reviews I got to my question above... I'm trying to keep things as clean as possible and separated in multiple questions. — Phrancis, Commented Sep 17, 2014 at 4:10

Stack Exchange Network

Revision 2 - Step 1: PsychoProductions management tool project

3 Answers 3

Final Schema!

The code

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql
postgresql
or ask your own question.

Linked

Hot Network Questions

Revision 2 - Step 1: PsychoProductions management tool project

3 Answers 3

Final Schema!

The code

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sqlpostgresql or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql
postgresql
or ask your own question.