Skip to main content
added 55 characters in body
Source Link
cimmanon
  • 3.7k
  • 16
  • 33
CREATE TABLE PersonRole(
    -- look, no serial
    PersonRoleName TEXT PRIMARY KEY
);
INSERT INTO PersonRole 
    (PersonRoleName)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;
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()
);
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
);
CREATE TABLE PersonRole(
    PersonRoleName TEXT PRIMARY KEY
);
INSERT INTO PersonRole 
    (PersonRoleName)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;
CREATE TABLE Person(
    PersonId SERIAL PRIMARY KEY,
    PersonRoleId TEXT NOT NULL DEFAULT 'Customer' 
        REFERENCES BillingMethod(PersonRoleName) 
    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()
);
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)
);
CREATE TABLE PersonRole(
    -- look, no serial
    PersonRoleName TEXT PRIMARY KEY
);
INSERT INTO PersonRole 
    (PersonRoleName)
VALUES
    ('Staff'),
    ('Partner'),
    ('Customer'),
    ('Vendor'),
    ('Session musician')
;
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()
);
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
);
Source Link
cimmanon
  • 3.7k
  • 16
  • 33

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(
    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) 
    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)
);

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.