Game Database Schema

This is the proposed schema for the lmce_game database. As the first version was used as is between the first two Game Player versions, the new version of the schema will be called v2.

As was before, this database is used to reconcile the possible metadata for game media found on a LinuxMCE system.

Goals

The goals with this particular database schema are:

• Deal with the differences between "Software List" based collections that are baked collections for a given emulator (e.g. MAME soft list), and loose ROMs.
• Try to consolidate media metadata to cleaned sanitized titles utilizing hashes made from normalized strings
• Consolidate and Handle multiple game systems

Tables

The Database is comprised of the following tables:

Attribute

This stores the type of attributes to keep for media metadata.

CREATE TABLE Attribute (
    PK_Attribute INTEGER AUTO_INCREMENT NOT NULL,
    Description VARCHAR(128),
    Define VARCHAR(128),
    PRIMARY KEY(Attribute)
);

File

This table is the entry point for a file, usually a ROM.

CREATE TABLE File  (
    PK_File INTEGER AUTO_INCREMENT NOT NULL,
    Description VARCHAR(1024),
    SHA1_Hash UNIQUE CHAR(40),
    PRIMARY_KEY(PK_File),
    KEY(SHA1_Hash)
);

GameSystem

A Game System is a particular system that can play a given game. Examples, Arcade, Atari 2600, NES, Sega Master System, etc.

CREATE TABLE GameSystem (
    PK_GameSystem INTEGER AUTO_INCREMENT NOT NULL,
    Description VARCHAR(128),
    PRIMARY KEY(PK_GameSystem)
);