forked from alexiscyber14/RUBY_CAPSTONE_PROJECT
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
80 lines (68 loc) · 2.19 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
CREATE TABLE items (
id SERIAL PRIMARY KEY,
published_date DATE,
archived BOOLEAN,
genre_id INT,
author_id INT,
label_id INT,
CONSTRAINT fk_genre_id FOREIGN KEY (genre_id) REFERENCES genre(id),
CONSTRAINT fk_author_id FOREIGN KEY (author_id) REFERENCES authors(id),
CONSTRAINT fk_label_id FOREIGN KEY (label_id) REFERENCES label(id)
);
CREATE INDEX idx_items_published_date ON items (published_date);
CREATE INDEX idx_items_genre_id ON items (genre_id);
CREATE INDEX idx_items_author_id ON items (author_id);
CREATE INDEX idx_items_label_id ON items (label_id);
CREATE TABLE label (
id SERIAL PRIMARY KEY,
title VARCHAR(150),
color VARCHAR(50),
item_id INT,
CONSTRAINT fk_label_item_id FOREIGN KEY (item_id) REFERENCES items(id)
);
CREATE TABLE book (
id SERIAL PRIMARY KEY,
publish_date DATE,
publisher VARCHAR(120),
cover_state VARCHAR(50),
archived BOOLEAN,
label_id INT,
CONSTRAINT fk_book_label_id FOREIGN KEY (label_id) REFERENCES label(id)
);
CREATE INDEX idx_book_label_id ON book (label_id);
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255)
);
CREATE TABLE games (
id SERIAL PRIMARY KEY,
title VARCHAR(150),
multiplayer BOOLEAN,
last_played_at DATE,
author_id INT,
CONSTRAINT fk_game_author_id FOREIGN KEY (author_id) REFERENCES authors(id)
);
CREATE INDEX idx_games_author_id ON games (author_id);
CREATE TABLE genre (
id SERIAL PRIMARY KEY,
name VARCHAR(150) NOT NULL
);
CREATE TABLE music_album (
id SERIAL PRIMARY KEY,
title VARCHAR(150) NOT NULL,
on_spotify BOOLEAN NOT NULL,
publish_date DATE NOT NULL,
genre_id INT,
CONSTRAINT fk_music_album_genre_id FOREIGN KEY (genre_id) REFERENCES genre(id)
);
CREATE INDEX idx_music_album_genre_id ON music_album (genre_id);
CREATE TABLE associations (
id SERIAL PRIMARY KEY,
item_id INT,
associated_item_id INT,
CONSTRAINT fk_associations_item_id FOREIGN KEY (item_id) REFERENCES items(id),
CONSTRAINT fk_associations_associated_item_id FOREIGN KEY (associated_item_id) REFERENCES items(id)
);
CREATE INDEX idx_associations_item_id ON associations (item_id);
CREATE INDEX idx_associations_associated_item_id ON associations (associated_item_id);