PostgreSQL: Basic Database User Schema

The following is a basic schema for creating a user authentication system in PostgreSQL. Adjust to your particular use case:

CREATE FUNCTION update_modified() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$;

CREATE TABLE users (
user_id integer NOT NULL,
username character varying(64) NOT NULL,
password character varying(255) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL,
activated smallint DEFAULT 0 NOT NULL,
banned smallint DEFAULT 1 NOT NULL
);

CREATE SEQUENCE users_user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;

SELECT pg_catalog.setval('users_user_id_seq', 1, false);

CREATE TABLE person (
person_id integer NOT NULL,
firstname character varying(64) NOT NULL,
lastname character varying(64) NOT NULL,
othername character varying(64) NOT NULL,
date_of_birth date NOT NULL,
gender character(1) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE person_address (
address_id integer NOT NULL,
tag character varying(24) NOT NULL,
address character varying(128) NOT NULL,
locality character varying(64) NOT NULL,
region character varying(32) NOT NULL,
country character varying(32) NOT NULL,
code character varying(8),
person_id integer NOT NULL
);

CREATE SEQUENCE person_address_address_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_address_address_id_seq OWNED BY person_address.address_id;

SELECT pg_catalog.setval('person_address_address_id_seq', 1, false);

CREATE TABLE person_email (
email_id integer NOT NULL,
tag character varying(24) NOT NULL,
email character varying(128),
person_id integer NOT NULL,
CONSTRAINT proper_email CHECK (((email)::text ~* '^[A-Za-z0-9._%-]+@(?:[A-Za-z0-9.-]+\.)+[A-Za-z]+::text'))
);

CREATE SEQUENCE person_email_email_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_email_email_id_seq OWNED BY person_email.email_id;

SELECT pg_catalog.setval('person_email_email_id_seq', 1, false);

CREATE SEQUENCE person_person_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_person_id_seq OWNED BY person.person_id;

SELECT pg_catalog.setval('person_person_id_seq', 1, false);

CREATE TABLE person_phone (
phone_id integer NOT NULL,
tag character varying(24) NOT NULL,
phone character varying(32),
person_id integer NOT NULL
);

CREATE SEQUENCE person_phone_phone_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_phone_phone_id_seq OWNED BY person_phone.phone_id;

SELECT pg_catalog.setval('person_phone_phone_id_seq', 1, false);

ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);

ALTER TABLE ONLY person ALTER COLUMN person_id SET DEFAULT nextval('person_person_id_seq'::regclass);

ALTER TABLE ONLY person_address ALTER COLUMN address_id SET DEFAULT nextval('person_address_address_id_seq'::regclass);

ALTER TABLE ONLY person_email ALTER COLUMN email_id SET DEFAULT nextval('person_email_email_id_seq'::regclass);

ALTER TABLE ONLY person_phone ALTER COLUMN phone_id SET DEFAULT nextval('person_phone_phone_id_seq'::regclass);

ALTER TABLE ONLY users
ADD CONSTRAINT user_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY users
ADD CONSTRAINT username_key UNIQUE (username);

ALTER TABLE ONLY person_email
ADD CONSTRAINT email_pkey PRIMARY KEY (email_id);

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_pkey PRIMARY KEY (address_id);

ALTER TABLE ONLY person
ADD CONSTRAINT person_pkey PRIMARY KEY (person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT phone_pkey PRIMARY KEY (phone_id);

CREATE TRIGGER update_users_modified BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_person_modified BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE update_modified();

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_email
ADD CONSTRAINT person_email_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT person_phone_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

You can check out the other parts of my PostgreSQL series including an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

References:

• Library of free database models from databaseanswers.org
• Moodle's user schema
• Schema for an invoice system by stackoverflow.com