design-example.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP SCHEMA IF EXISTS design CASCADE;

CREATE SCHEMA IF NOT EXISTS design;

CREATE TABLE IF NOT EXISTS  design.event(
	id uuid  DEFAULT uuid_generate_v4() PRIMARY KEY,
	name TEXT
);

CREATE TABLE IF NOT EXISTS design.speaker(
	id uuid  DEFAULT uuid_generate_v4() PRIMARY KEY,
	name TEXT
);

CREATE TABLE IF NOT EXISTS design.event(
	id uuid  DEFAULT uuid_generate_v4() PRIMARY KEY,
	name TEXT
);

CREATE TABLE IF NOT EXISTS design.user(
	id uuid  DEFAULT uuid_generate_v4() PRIMARY KEY,
	name TEXT
);

CREATE TABLE IF NOT EXISTS design.social_link(
	id uuid  DEFAULT uuid_generate_v4() PRIMARY KEY,
	name TEXT,
	regexp_validation TEXT DEFAULT '^https?:\/\/'
);

-- CONFIGS
CREATE TABLE IF NOT EXISTS design.event_social_link_config(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	event_id uuid REFERENCES design.event(id) ON DELETE CASCADE NOT NULL,
	optional BOOLEAN DEFAULT FALSE NOT NULL,
	CONSTRAINT unique_event_social_link_config UNIQUE (social_link_id, event_id)
);

CREATE TABLE IF NOT EXISTS design.speaker_social_link_config(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	speaker_id uuid REFERENCES design.speaker(id) ON DELETE CASCADE NOT NULL,
	optional BOOLEAN DEFAULT FALSE NOT NULL,
	CONSTRAINT unique_speaker_social_link_config UNIQUE (social_link_id, speaker_id)

);

CREATE TABLE IF NOT EXISTS design.user_social_link_config(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	user_id uuid REFERENCES design.user(id) ON DELETE CASCADE NOT NULL,
	optional BOOLEAN DEFAULT FALSE NOT NULL,
	CONSTRAINT unique_user_social_link_config UNIQUE (social_link_id, user_id)
);

-- RELATIONSHIPS
CREATE TABLE IF NOT EXISTS design.event_social_link(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	event_id uuid REFERENCES design.event(id) ON DELETE CASCADE NOT NULL,
	value TEXT NOT NULL,
	CONSTRAINT unique_event_social_link UNIQUE (social_link_id, event_id)
);

CREATE TABLE IF NOT EXISTS design.speaker_social_link(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	speaker_id uuid REFERENCES design.speaker(id) ON DELETE CASCADE NOT NULL,
	value TEXT NOT NULL,
	CONSTRAINT unique_speaker_social_link UNIQUE (social_link_id, speaker_id)

);

CREATE TABLE IF NOT EXISTS design.user_social_link(
	social_link_id uuid REFERENCES design.social_link(id) ON DELETE CASCADE NOT NULL,
	user_id uuid REFERENCES design.user(id) ON DELETE CASCADE NOT NULL,
	value TEXT NOT NULL,
	CONSTRAINT unique_user_social_link UNIQUE (social_link_id, user_id)
);