68 lines
1.5 KiB
SQL
68 lines
1.5 KiB
SQL
CREATE TABLE category
|
|
(id text PRIMARY KEY,
|
|
name text NOT NULL);
|
|
|
|
CREATE TABLE placement
|
|
(id SERIAL PRIMARY KEY,
|
|
category text NOT NULL REFERENCES category (id),
|
|
shelf text NOT NULL);
|
|
|
|
CREATE TABLE bookseries
|
|
(id text PRIMARY KEY,
|
|
title text NOT NULL);
|
|
|
|
CREATE TABLE book
|
|
(isbn text PRIMARY KEY,
|
|
id text,
|
|
title text NOT NULL,
|
|
subtitle text,
|
|
category text NOT NULL REFERENCES category (id),
|
|
publisher text,
|
|
published_year integer,
|
|
edition text,
|
|
pages integer,
|
|
series text REFERENCES bookseries (id),
|
|
description text); -- TODO: bilde?
|
|
|
|
CREATE TABLE alternative_title
|
|
(id SERIAL PRIMARY KEY,
|
|
book text NOT NULL REFERENCES book (isbn),
|
|
alt_title text NOT NULL);
|
|
|
|
CREATE TABLE copy
|
|
(book text REFERENCES book (isbn),
|
|
number integer,
|
|
owner text,
|
|
PRIMARY KEY(book, number));
|
|
|
|
CREATE TABLE person
|
|
(id text PRIMARY KEY,
|
|
lastname text NOT NULL,
|
|
firstname text NOT NULL);
|
|
|
|
CREATE TABLE bookrole
|
|
(id text PRIMARY KEY,
|
|
name text NOT NULL);
|
|
|
|
CREATE TABLE bookperson
|
|
(id SERIAL PRIMARY KEY,
|
|
book text NOT NULL REFERENCES book (isbn),
|
|
person text NOT NULL REFERENCES person (id),
|
|
relation text NOT NULL REFERENCES bookrole (id));
|
|
|
|
CREATE TABLE referencetype
|
|
(id text PRIMARY KEY,
|
|
name text NOT NULL);
|
|
|
|
CREATE TABLE bookreference
|
|
(id SERIAL PRIMARY KEY,
|
|
book text NOT NULL REFERENCES book (isbn),
|
|
reftype text NOT NULL REFERENCES referencetype (id),
|
|
value text NOT NULL);
|
|
|
|
CREATE TABLE personreference
|
|
(id SERIAL PRIMARY KEY,
|
|
person text NOT NULL REFERENCES person (id),
|
|
reftype text NOT NULL REFERENCES referencetype (id),
|
|
value text NOT NULL);
|