schema.sql 1.52 KB
Newer Older
1
DROP TABLE IF EXISTS "non_proprietary_names";
2
DROP TABLE IF EXISTS "substances";
3
DROP TABLE IF EXISTS "entries";
4

Rob Tomsick's avatar
Rob Tomsick committed
5
CREATE TABLE "entries" (
6
	"id"				UUID PRIMARY KEY NOT NULL,
7
	"product_id"		VARCHAR(256) NOT NULL,
8
	"ndc"				VARCHAR(14) NOT NULL,
9
	"dosage_form"		VARCHAR(256) NOT NULL,
10 11
	"proprietary_name"	VARCHAR(512) NOT NULL,
	"phonetic_hash"		VARCHAR(32) NOT NULL
12 13 14 15
);
CREATE INDEX IF NOT EXISTS "entries_n_idx" ON "entries" ("ndc");
CREATE INDEX IF NOT EXISTS "entries_d_idx" ON "entries" ("dosage_form");
CREATE INDEX IF NOT EXISTS "entries_pn_idx" ON "entries" ("proprietary_name");
16
CREATE INDEX IF NOT EXISTS "entries_pn_hash" ON "entries" ("phonetic_hash");
17

Rob Tomsick's avatar
Rob Tomsick committed
18 19
CREATE TABLE "non_proprietary_names" (
	"id__entries"		UUID NOT NULL,
20 21
	"name"				VARCHAR(512) NOT NULL,
	"phonetic_hash"		VARCHAR(32) NOT NULL,
22 23 24 25 26

	FOREIGN KEY ("id__entries") REFERENCES "entries" ("id")
);
CREATE INDEX IF NOT EXISTS "npn_id" ON "non_proprietary_names" ("id__entries");
CREATE INDEX IF NOT EXISTS "npn_name" ON "non_proprietary_names" ("name");
27
CREATE INDEX IF NOT EXISTS "npn_pn_hash" ON "non_proprietary_names" ("phonetic_hash");
28

Rob Tomsick's avatar
Rob Tomsick committed
29 30
CREATE TABLE "substances" (
	"id__entries"		UUID NOT NULL,
31 32
	"name"				VARCHAR(512) NOT NULL,
	"phonetic_hash"		VARCHAR(32) NOT NULL,
33 34 35

	FOREIGN KEY ("id__entries") REFERENCES "entries" ("id")
);
36
CREATE INDEX IF NOT EXISTS "substance_entry_id_fkey_idx" ON "substances" ("id__entries");
37
CREATE INDEX IF NOT EXISTS "substance_name_idx" ON "substances" ("name");
38 39
CREATE INDEX IF NOT EXISTS "substance_pn_hash" ON "substances" ("phonetic_hash");