PG Library

A group of PostgreSQL scripts that setup a database for a fictional library. Having worked with SQL before, I wanted to learn PostgreSQL, and I decided that designing a database for a fictional library would be a fun challenge. Below I describe the features, motivate the design choices I made, and describe the specific tables and columns I included.

Features

Rebuild database (rebuild_database.sql)

This script drops all tables and recreates them. This is useful during development, but dangerous in production!

Loan procedures (loan_procedures.sql)

This script creates two callable procedures that automate the processes of loaning and returning a piece of media. The user only needs to specify the id of the media and the user that is making the loan.

Summary view (views.sql)

This script creates a couple of views for the database that are useful. For example a view of all currently active loans, the number of books in various subjects, employees per department, etc.

Test data generator

I have included a python script that generates data for development purposes.

Database Design

For this project, I wanted a compromise between database complexity and employee usability. All tables use automatically incremented integers as primary key which allows for convenient references, and does not require storing long convoluted UUID strings. As an additional challenge, I wanted this library to reflect the large infrastructure of a national library. I have therefore included "departments", which are essentially different branches of the library organization. A department could be a specific library such as in a city, but also a specific sub-organization such as "management", or "transportation". This allows the database to describe an internal hierarchy, where deparments report to other departments, and different types of departments can be distinguished. In addition, one department may have several "locations", which are specific buildings, or annexes with individual addresses. This allows us to distinguish between different locations that are part of the same department, for example two buildings in a city that function as the same library in the organizational structure.

In addition, I have in some tables included columns for "codenames", i.e. abbreviations which may be useful when departments and locations have long names. For example "East Oregon Contemporary Library of the Culinary Mind" (EO-CLCM).

Employee table (employees)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
firstname	text		The employees first name
lastname	text		The employees last name
department_id	integer		The id of the department where the employee works.
phonenumber	text		The work phone number of the employee
hiredate	date		The date when the employee started working
enddate	date		The date when the employee stopped working if applicable
jobtitle	text		The job title of the employee
gender	text		The employees gender
birthdate	date		The birthdate of the employee
salary	numeric	Not null	The salary of the employee
salarycurrency	text	Not null	The currency in which the salary is given

Department table (departments)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
fullname	text	Not null, unique
codename	text	Not null, unique
manager_id	integer	Not null
description	text
admindepartment_id	integer	Not null

Location table (locations)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
department_id	integer	Not null
codename	text	Not null, unique
type	text	Not null
city	text	Not null
address	text	Not null

Books and media table (media)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
isbn	integer	Not null, unique
title	text	Not null
author_id	integer	Not null
publisher_id	integer
location_id	integer
pages	integer	Default null
language	text	Default null
subjects	array of texts

Serials table (serials)

Each instance represents a "serial" of media, for example a trilogy of books.

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key

Author table (authors)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
firstname	text	Not null
lastname	text	Not null
gender	text	Not null
birthdate	date	Default null
deathdate	date	Default null

Publisher table (publishers)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
name	text	Not null
codename	text	Unique

Subjects table (subjects)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
name	text	Not null
codename	text	Unique

Users table (users)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
identificationnumber	text	Not null
firstname	text	Not null
lastname	text	Not null
phonenumber	text
email	text
usersince	date
banned	boolean	Not null, default false

Loans table (loans)

Column	Type	Restriction	Description
id	integer	Not null, unique	The primary key
media_id	integer	Not null
user_id	integer	Not null
loandate	date	Not null
duedate	date	Not null
resolved	boolean	Not null, default false