CREATE SCHEMA i18n;

-- Create the translated_text data type - the implementation is just an array of text items,
--	but that should not be visible outside the "library"
CREATE DOMAIN i18n.translated_text
	AS text[];

-- Create a "language" table to store the languages available in the system
CREATE TABLE i18n.language
(
    id Serial NOT NULL PRIMARY KEY, -- integer used as the key for the under-lying arrays, and should never be manually updated
    code Text NOT NULL, -- code used to access translations, such as an ISO 6639-1 2-letter code or IETF language-country tag
    name i18n.translated_text NOT NULL, -- the name of the language is, of course, translatable! ;)
    fallback_id Integer NULL -- self-referencing key which will be used (recursively) if no translation is found in the requested language
    				-- be careful not to set up loops! 
);

CREATE UNIQUE INDEX uq_language_code
	ON i18n.language (code);

ALTER TABLE i18n.language
    ADD CONSTRAINT fk_language_fallback_id_self
    FOREIGN KEY (fallback_id) REFERENCES i18n.language (id);