Context Navigation

source: schema_creation (1).sql@ b374c85

main

Last change on this file since b374c85 was d8deee6, checked in by GitHub <noreply@…>, 3 months ago
Add files via upload
Property mode set to `100644`
File size: 11.6 KB

Rev	Line
[d8deee6]	1	-- =============================================================
	2	-- Wedding Planner
	3	-- =============================================================
	4
	5	DROP SCHEMA IF EXISTS project CASCADE;
	6	CREATE SCHEMA project;
	7	SET search_path TO project;
	8
	9	-- ── Users ──────────────────────────────────────────────────
	10	CREATE TABLE "user" (
	11	user_id SERIAL PRIMARY KEY,
	12	first_name VARCHAR(50) NOT NULL,
	13	last_name VARCHAR(50) NOT NULL,
	14	email VARCHAR(120) NOT NULL UNIQUE,
	15	password_hash VARCHAR(255) NOT NULL DEFAULT '', -- added for login
	16	phone_number VARCHAR(30),
	17	gender VARCHAR(20),
	18	birthday DATE
	19	);
	20
	21	-- ── Weddings ────────────────────────────────────────────────
	22	CREATE TABLE wedding (
	23	wedding_id SERIAL PRIMARY KEY,
	24	"date" DATE NOT NULL,
	25	budget NUMERIC(12,2),
	26	notes TEXT,
	27	user_id INTEGER NOT NULL,
	28	CONSTRAINT fk_wedding_user
	29	FOREIGN KEY (user_id) REFERENCES "user"(user_id)
	30	ON UPDATE CASCADE ON DELETE CASCADE
	31	);
	32
	33	-- ── Churches ────────────────────────────────────────────────
	34	CREATE TABLE church (
	35	church_id SERIAL PRIMARY KEY,
	36	name VARCHAR(120) NOT NULL,
	37	location VARCHAR(150) NOT NULL,
	38	contact VARCHAR(120) NOT NULL,
	39	wedding_id INTEGER UNIQUE,
	40	CONSTRAINT fk_church_wedding
	41	FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id)
	42	ON UPDATE CASCADE ON DELETE SET NULL
	43	);
	44
	45	-- ── Priests ─────────────────────────────────────────────────
	46	CREATE TABLE priest (
	47	priest_id SERIAL PRIMARY KEY,
	48	name VARCHAR(120) NOT NULL,
	49	contact VARCHAR(120) NOT NULL,
	50	church_id INTEGER NOT NULL,
	51	CONSTRAINT fk_priest_church
	52	FOREIGN KEY (church_id) REFERENCES church(church_id)
	53	ON UPDATE CASCADE ON DELETE CASCADE
	54	);
	55
	56	-- ── Church Bookings ─────────────────────────────────────────
	57	CREATE TABLE church_booking (
	58	booking_id SERIAL PRIMARY KEY,
	59	"date" DATE NOT NULL,
	60	start_time TIME NOT NULL,
	61	end_time TIME NOT NULL,
	62	status VARCHAR(30) NOT NULL,
	63	church_id INTEGER NOT NULL,
	64	wedding_id INTEGER NOT NULL,
	65	CONSTRAINT fk_cb_church FOREIGN KEY (church_id) REFERENCES church(church_id) ON UPDATE CASCADE ON DELETE CASCADE,
	66	CONSTRAINT fk_cb_wedding FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id) ON UPDATE CASCADE ON DELETE CASCADE,
	67	CONSTRAINT chk_cb_time CHECK (end_time > start_time)
	68	);
	69
	70	-- ── Events ──────────────────────────────────────────────────
	71	CREATE TABLE event (
	72	event_id SERIAL PRIMARY KEY,
	73	event_type VARCHAR(60) NOT NULL,
	74	"date" DATE NOT NULL,
	75	start_time TIME NOT NULL,
	76	end_time TIME NOT NULL,
	77	status VARCHAR(30) NOT NULL,
	78	wedding_id INTEGER NOT NULL,
	79	CONSTRAINT fk_event_wedding
	80	FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id)
	81	ON UPDATE CASCADE ON DELETE CASCADE,
	82	CONSTRAINT chk_event_time CHECK (end_time > start_time)
	83	);
	84
	85	-- ── Guests ──────────────────────────────────────────────────
	86	CREATE TABLE guest (
	87	guest_id SERIAL PRIMARY KEY,
	88	first_name VARCHAR(50) NOT NULL,
	89	last_name VARCHAR(50) NOT NULL,
	90	email VARCHAR(120),
	91	wedding_id INTEGER NOT NULL,
	92	CONSTRAINT fk_guest_wedding
	93	FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id)
	94	ON UPDATE CASCADE ON DELETE CASCADE
	95	);
	96
	97	-- ── RSVP ────────────────────────────────────────────────────
	98	CREATE TABLE event_rsvp (
	99	response_id SERIAL PRIMARY KEY,
	100	status VARCHAR(30) NOT NULL,
	101	response_date DATE NOT NULL,
	102	guest_id INTEGER NOT NULL,
	103	event_id INTEGER NOT NULL,
	104	CONSTRAINT fk_rsvp_guest FOREIGN KEY (guest_id) REFERENCES guest(guest_id) ON UPDATE CASCADE ON DELETE CASCADE,
	105	CONSTRAINT fk_rsvp_event FOREIGN KEY (event_id) REFERENCES event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
	106	CONSTRAINT uq_rsvp_guest_event UNIQUE (guest_id, event_id)
	107	);
	108
	109	-- ── Attendance / Seating ─────────────────────────────────────
	110	CREATE TABLE attendance (
	111	attendance_id SERIAL PRIMARY KEY,
	112	status VARCHAR(30) NOT NULL,
	113	table_number INTEGER,
	114	role VARCHAR(40) NOT NULL,
	115	guest_id INTEGER NOT NULL,
	116	event_id INTEGER NOT NULL,
	117	CONSTRAINT fk_att_guest FOREIGN KEY (guest_id) REFERENCES guest(guest_id) ON UPDATE CASCADE ON DELETE CASCADE,
	118	CONSTRAINT fk_att_event FOREIGN KEY (event_id) REFERENCES event(event_id) ON UPDATE CASCADE ON DELETE CASCADE,
	119	CONSTRAINT uq_att_guest_event UNIQUE (guest_id, event_id),
	120	CONSTRAINT chk_table_number CHECK (table_number IS NULL OR table_number > 0)
	121	);
	122
	123	-- ── Venue Types ─────────────────────────────────────────────
	124	CREATE TABLE venue_type (
	125	type_id SERIAL PRIMARY KEY,
	126	type_name VARCHAR(50) NOT NULL UNIQUE
	127	);
	128
	129	-- ── Venues ──────────────────────────────────────────────────
	130	CREATE TABLE venue (
	131	venue_id SERIAL PRIMARY KEY,
	132	name VARCHAR(120) NOT NULL,
	133	location VARCHAR(150) NOT NULL,
	134	city VARCHAR(80) NOT NULL,
	135	address VARCHAR(150) NOT NULL,
	136	capacity INTEGER NOT NULL,
	137	menu TEXT,
	138	phone_number VARCHAR(30),
	139	price_per_guest NUMERIC(10,2) NOT NULL,
	140	type_id INTEGER NOT NULL,
	141	CONSTRAINT fk_venue_type FOREIGN KEY (type_id) REFERENCES venue_type(type_id) ON UPDATE CASCADE ON DELETE RESTRICT,
	142	CONSTRAINT chk_capacity CHECK (capacity > 0),
	143	CONSTRAINT chk_price_per_guest CHECK (price_per_guest >= 0)
	144	);
	145
	146	-- ── Venue Bookings ───────────────────────────────────────────
	147	CREATE TABLE venue_booking (
	148	booking_id SERIAL PRIMARY KEY,
	149	"date" DATE NOT NULL,
	150	start_time TIME NOT NULL,
	151	end_time TIME NOT NULL,
	152	status VARCHAR(30) NOT NULL,
	153	price NUMERIC(12,2) NOT NULL,
	154	venue_id INTEGER NOT NULL,
	155	wedding_id INTEGER NOT NULL,
	156	CONSTRAINT fk_vb_venue FOREIGN KEY (venue_id) REFERENCES venue(venue_id) ON UPDATE CASCADE ON DELETE CASCADE,
	157	CONSTRAINT fk_vb_wedding FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id) ON UPDATE CASCADE ON DELETE CASCADE,
	158	CONSTRAINT chk_vb_time CHECK (end_time > start_time)
	159	);
	160
	161	-- ── Photographers ────────────────────────────────────────────
	162	CREATE TABLE photographer (
	163	photographer_id SERIAL PRIMARY KEY,
	164	name VARCHAR(120) NOT NULL,
	165	email VARCHAR(120) NOT NULL UNIQUE,
	166	phone_number VARCHAR(30) NOT NULL,
	167	price_per_hour NUMERIC(10,2) NOT NULL,
	168	CONSTRAINT chk_ph_price CHECK (price_per_hour >= 0)
	169	);
	170
	171	-- ── Photographer Bookings ────────────────────────────────────
	172	CREATE TABLE photographer_booking (
	173	booking_id SERIAL PRIMARY KEY,
	174	"date" DATE NOT NULL,
	175	start_time TIME NOT NULL,
	176	end_time TIME NOT NULL,
	177	status VARCHAR(30) NOT NULL,
	178	photographer_id INTEGER NOT NULL,
	179	wedding_id INTEGER NOT NULL,
	180	CONSTRAINT fk_pb_photographer FOREIGN KEY (photographer_id) REFERENCES photographer(photographer_id) ON UPDATE CASCADE ON DELETE CASCADE,
	181	CONSTRAINT fk_pb_wedding FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id) ON UPDATE CASCADE ON DELETE CASCADE,
	182	CONSTRAINT chk_pb_time CHECK (end_time > start_time)
	183	);
	184
	185	-- ── Bands ────────────────────────────────────────────────────
	186	CREATE TABLE band (
	187	band_id SERIAL PRIMARY KEY,
	188	band_name VARCHAR(120) NOT NULL,
	189	genre VARCHAR(60) NOT NULL,
	190	equipment TEXT,
	191	phone_number VARCHAR(30) NOT NULL,
	192	price_per_hour NUMERIC(10,2) NOT NULL,
	193	CONSTRAINT chk_band_price CHECK (price_per_hour >= 0)
	194	);
	195
	196	-- ── Band Bookings ────────────────────────────────────────────
	197	CREATE TABLE band_booking (
	198	booking_id SERIAL PRIMARY KEY,
	199	"date" DATE NOT NULL,
	200	start_time TIME NOT NULL,
	201	end_time TIME NOT NULL,
	202	status VARCHAR(30) NOT NULL,
	203	band_id INTEGER NOT NULL,
	204	wedding_id INTEGER NOT NULL,
	205	CONSTRAINT fk_bb_band FOREIGN KEY (band_id) REFERENCES band(band_id) ON UPDATE CASCADE ON DELETE CASCADE,
	206	CONSTRAINT fk_bb_wedding FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id) ON UPDATE CASCADE ON DELETE CASCADE,
	207	CONSTRAINT chk_bb_time CHECK (end_time > start_time)
	208	);
	209
	210	-- ── Registrars (NEW — was in HTML but missing from original schema) ──
	211	CREATE TABLE registrar (
	212	registrar_id SERIAL PRIMARY KEY,
	213	name VARCHAR(120) NOT NULL,
	214	contact VARCHAR(120) NOT NULL,
	215	location VARCHAR(150) NOT NULL,
	216	working_hours VARCHAR(60)
	217	);
	218
	219	-- ── Registrar Bookings (NEW) ─────────────────────────────────
	220	CREATE TABLE registrar_booking (
	221	booking_id SERIAL PRIMARY KEY,
	222	"date" DATE NOT NULL,
	223	start_time TIME NOT NULL,
	224	end_time TIME NOT NULL,
	225	status VARCHAR(30) NOT NULL,
	226	registrar_id INTEGER NOT NULL,
	227	wedding_id INTEGER NOT NULL,
	228	CONSTRAINT fk_rb_registrar FOREIGN KEY (registrar_id) REFERENCES registrar(registrar_id) ON UPDATE CASCADE ON DELETE CASCADE,
	229	CONSTRAINT fk_rb_wedding FOREIGN KEY (wedding_id) REFERENCES wedding(wedding_id) ON UPDATE CASCADE ON DELETE CASCADE,
	230	CONSTRAINT chk_rb_time CHECK (end_time > start_time)
	231	);
	232
	233	-- ── Indexes ──────────────────────────────────────────────────
	234	CREATE INDEX idx_wedding_user ON wedding(user_id);
	235	CREATE INDEX idx_event_wedding ON event(wedding_id);
	236	CREATE INDEX idx_guest_wedding ON guest(wedding_id);
	237	CREATE INDEX idx_vb_wedding ON venue_booking(wedding_id);
	238	CREATE INDEX idx_vb_venue ON venue_booking(venue_id);
	239	CREATE INDEX idx_pb_wedding ON photographer_booking(wedding_id);
	240	CREATE INDEX idx_bb_wedding ON band_booking(wedding_id);
	241	CREATE INDEX idx_rb_wedding ON registrar_booking(wedding_id);
	242	CREATE INDEX idx_rsvp_event ON event_rsvp(event_id);
	243	CREATE INDEX idx_att_event ON attendance(event_id);

Note: See TracBrowser for help on using the repository browser.

Download in other formats: