File: i18n.sql

CREATE TABLE i18n_language (
	id INT NOT NULL AUTO_INCREMENT,
	iso639_1 CHAR(2) NOT NULL,
	active CHAR(1) NOT NULL DEFAULT 'Y',
	
	PRIMARY KEY(id),
	UNIQUE(iso639_1)
);

CREATE TABLE i18n_language_name (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	language_parent INT NOT NULL,
	language INT NOT NULL,

	PRIMARY KEY(id),
	FOREIGN KEY(language_parent) REFERENCES i18n_language(id),
	FOREIGN KEY(language) REFERENCES i18n_language(id)
);

CREATE TABLE i18n_locale (
	id INT NOT NULL AUTO_INCREMENT,
	iso3166_1 CHAR(2) NOT NULL,
	active CHAR(1) NOT NULL DEFAULT 'Y',
	language INT NOT NULL,
	
	PRIMARY KEY(id),
	FOREIGN KEY(language) REFERENCES i18n_language(id),
	UNIQUE(iso3166_1)
);

CREATE TABLE i18n_locale_name (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	locale INT NOT NULL,
	language INT NOT NULL,

	PRIMARY KEY(id),
	FOREIGN KEY(locale) REFERENCES i18n_locale(id),
	FOREIGN KEY(language) REFERENCES i18n_language(id)
);

CREATE TABLE i18n_container (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,

	PRIMARY KEY(id),
	UNIQUE(name)
);

CREATE TABLE i18n_section (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	container INT NOT NULL,

	PRIMARY KEY(id),
	UNIQUE(name),
	FOREIGN KEY(container) REFERENCES i18n_container(id)
);

CREATE TABLE i18n_element (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	section INT NOT NULL,

	PRIMARY KEY(id),
	UNIQUE(name),
	FOREIGN KEY(section) REFERENCES i18n_section(id)
);

CREATE TABLE i18n_element_content (
	id INT NOT NULL AUTO_INCREMENT,
	content TEXT DEFAULT NULL,
	element INT NOT NULL,
	language INT NOT NULL,
	locale INT DEFAULT NULL,

	PRIMARY KEY(id),
	FOREIGN KEY(element) REFERENCES i18n_element(id),
	FOREIGN KEY(language) REFERENCES i18n_language(id)
);