CREATE TABLE attachment (
	id SERIAL PRIMARY KEY,
	created TIMESTAMP DEFAULT NOW(),
	last_modified TIMESTAMP DEFAULT NOW(),
	description VARCHAR(64),
	content_type VARCHAR(32) NOT NULL,
	data_id OID NOT NULL
);

CREATE TABLE invention (
	id SERIAL PRIMARY KEY,
	title VARCHAR(64) NOT NULL,
	used_in_product VARCHAR(32),
	used_in_which_products VARCHAR,
	used_in_standard VARCHAR,
	problem_addressed TEXT,
	technical_details INTEGER FOREIGN KEY REFERENCES attachment (id),
	prior_art INTEGER FOREIGN KEY REFERENCES attachment (id),
	prior_art_advantages INTEGER FOREIGN KEY REFERENCES attachment (id),
	conception DATE,
	invention_record INTEGER FOREIGN KEY REFERENCES attachment (id),
	past_disclosure INTEGER FOREIGN KEY REFERENCES attachment (id),
	future_disclosure INTEGER FOREIGN KEY REFERENCES attachment (id),
	govt_contracts INTEGER FOREIGN KEY REFERENCES attachment (id),
	likely_evil_interest VARCHAR(32),
	likely_evil_interest_sources INTEGER FOREIGN KEY REFERENCES attachment (id),
	detectability VARCHAR(32),
	design_alternatives INTEGER FOREIGN KEY REFERENCES attachment (id)
);

CREATE TABLE inventor_list (
	inventor_id INTEGER NOT NULL, --- References userid in the Fedora account system.
	invention_id INTEGER FOREIGN KEY REFERENCES invention (id) NOT NULL,
	PRIMARY KEY (inventor_id, invention_id)
);
