create table ATC (
	code char(8) primary key,
	description text
);

comment on table ATC is
'ATC code (http://www.whocc.no/atcddd/)';



create table drug (
	id serial primary key,
	ATC char(8),
	compound boolean default 'f',
	INN varchar(255)
);

comment on table drug is
'a drug may consist of either a single or multiple active ingredients';

comment on column drug.ATC is
'ATC code (http://www.whocc.no/atcddd/)';

comment on column drug.compound is
'"t" if this drug consist of multiple active ingredients, else "f"';

comment on column drug.INN is
'international non-proprietary name = generic name. Other names see table names_drug';



create table drug_components (
	id_drug integer references drug(id),
	id_component integer references drug(id)
);

comment on table compound_drug is
'many-2-many pivot table linking components of a compound drug.';

comment on column drug_components.id_drug is
'id of the dominant component, or of the alphabetically first on if there is no dominant component';

comment on column drug_components.id_component is
'id of a component of ths drug';



create table names_drug(
	country char(2),
	id_drug integer references drug(id),
	name varchar(255)
);

comment on table names_drug is
'allows to allocate country specific synonyms to a drug name';

comment on column names_drug.country is
'Two character ISO country code';