Investigate sqlite

[zoispag]

CREATE TABLE `sources` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`name` TEXT NOT NULL,
	`url` TEXT NOT NULL
);

CREATE TABLE `consoles` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`name` TEXT NOT NULL UNIQUE
);

CREATE TABLE `console_source` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`console_id` int NOT NULL,
	`source_id` int NOT NULL,
	FOREIGN KEY(console_id) REFERENCES consoles(id),
	FOREIGN KEY(source_id) REFERENCES sources(id)
);

CREATE TABLE `roms` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`name` TEXT NOT NULL,
	`console_id` int NOT NULL,
	`language` TEXT NOT NULL,
	`year` int(4),
	`version` TEXT,
	FOREIGN KEY(console_id) REFERENCES consoles(id)
);

CREATE TABLE `rom_urls` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`rom_id` bigint NOT NULL,
	`source_id` int NOT NULL,
	`url` TEXT NOT NULL,
	`download_url` TEXT NOT NULL,
	FOREIGN KEY(source_id) REFERENCES sources(id),
	FOREIGN KEY(rom_id) REFERENCES roms(id)
);