-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_tables.sql
59 lines (46 loc) · 1.24 KB
/
create_tables.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
-- Resets postgres with the example tables
DROP TABLE IF EXISTS container;
DROP TABLE IF EXISTS computer;
DROP TABLE IF EXISTS freezer;
DROP TABLE IF EXISTS site CASCADE;
CREATE TABLE site (
id serial PRIMARY KEY,
-- Arbitrary name of the site
name text,
-- Address info. It is purposely kept minimally constrained and not US specific
-- Preferred name for the address. UTF-8 is used so special characters are OK
contact text,
-- Freeform address text in case of non-US or other special issues
address text,
state_or_region text,
-- ISO 3166 validation in app to help highlight
country text,
postcode_or_zip text
);
CREATE TABLE computer (
id serial PRIMARY KEY,
name text,
-- 48 bit IEEE 802 mac address; could swith to mac8, if needed
mac macaddr UNIQUE,
-- site's have a list of computers
site_id INT NOT NULL,
FOREIGN KEY (site_id)
REFERENCES site (id)
);
CREATE TABLE freezer (
id serial PRIMARY KEY,
name text,
-- site's have a list of freezers
site_id INT NOT NULL,
FOREIGN KEY (site_id)
REFERENCES site (id)
);
CREATE TABLE container (
id serial PRIMARY KEY,
uuid UUID UNIQUE,
description text,
-- freezer's have a list of containers
freezer_id INT NOT NULL,
FOREIGN KEY (freezer_id)
REFERENCES freezer (id)
);