--
-- DRAFT 8/20/04 - NSRL table structure proposed for Sep. 2004
--

--
-- Table structure for table `Application`
--

CREATE TABLE Application (
  id int(11) NOT NULL auto_increment,
  code int(11) NOT NULL default '0',
  name varchar(150) NOT NULL default '',
  version varchar(150) NOT NULL default '',
  barcode varchar(50) NOT NULL default '',
  comment varchar(250) default NULL,
  location varchar(50) NOT NULL default '',
  creation_date timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY code (code)
) TYPE=MyISAM;

--
-- Table structure for table `ApplicationType`
--

CREATE TABLE ApplicationType (
  id int(11) NOT NULL auto_increment,
  type varchar(100) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY type (type)
) TYPE=MyISAM;

--
-- Table structure for table `Contact`
--

CREATE TABLE Contact (
  id int(11) NOT NULL auto_increment,
  name varchar(150) NOT NULL default '',
  type varchar(15) NOT NULL default '',
  email varchar(80) default NULL,
  web_url varchar(150) default NULL,
  telephone varchar(40) default NULL,
  address1 varchar(50) default NULL,
  address2 varchar(50) default NULL,
  city varchar(20) default NULL,
  stateprov varchar(50) default NULL,
  postal_code varchar(10) default NULL,
  country varchar(50) default NULL,
  comment varchar(250) default NULL,
  creation_date timestamp(14) NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Table structure for table `File`
--

CREATE TABLE File (
  id int(11) NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  path varchar(50) NOT NULL default '',
  ext varchar(255) default NULL,
  size bigint(20) NOT NULL default '0',
  sha varchar(40) NOT NULL default '',
  md4 varchar(32) NOT NULL default '',
  md5 varchar(32) NOT NULL default '',
  crc32 varchar(8) NOT NULL default '',
  sha256 varchar(64) default NULL,
  file_date datetime default NULL,
  modification_time timestamp(14) NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Table structure for table `FileType`
--

CREATE TABLE FileType (
  id int(11) NOT NULL auto_increment,
  type varchar(15) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY name (type)
) TYPE=MyISAM;

--
-- Table structure for table `Language`
--

CREATE TABLE Language (
  id int(11) NOT NULL auto_increment,
  name varchar(150) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) TYPE=MyISAM;

--
-- Table structure for table `LinkApplicationToApplication`
--

CREATE TABLE LinkApplicationToApplication (
  parent_application_id int(11) NOT NULL default '0',
  child_application_id int(11) NOT NULL default '0',
  PRIMARY KEY  (parent_application_id,child_application_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkApplicationToApplicationType`
--

CREATE TABLE LinkApplicationToApplicationType (
  application_id int(11) NOT NULL default '0',
  application_type_id int(11) NOT NULL default '0',
  PRIMARY KEY  (application_id,application_type_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkApplicationToLanguage`
--

CREATE TABLE LinkApplicationToLanguage (
  application_id int(11) NOT NULL default '0',
  language_id int(11) NOT NULL default '0',
  PRIMARY KEY  (application_id,language_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkApplicationToManufacturer`
--

CREATE TABLE LinkApplicationToManufacturer (
  application_id int(11) NOT NULL default '0',
  manufacturer_id int(11) NOT NULL default '0',
  PRIMARY KEY  (application_id,manufacturer_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkApplicationToOperatingSystem`
--

CREATE TABLE LinkApplicationToOperatingSystem (
  application_id int(11) NOT NULL default '0',
  operating_system_id int(11) NOT NULL default '0',
  PRIMARY KEY  (application_id,operating_system_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkFileToApplication`
--

CREATE TABLE LinkFileToApplication (
  file_id int(11) NOT NULL default '0',
  application_id int(11) NOT NULL default '0',
  PRIMARY KEY  (file_id,application_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkFileToFileType`
--

CREATE TABLE LinkFileToFileType (
  file_id int(11) NOT NULL default '0',
  file_type_id int(11) NOT NULL default '0',
  PRIMARY KEY  (file_id,file_type_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkFileToMedia`
--

CREATE TABLE LinkFileToMedia (
  file_id int(11) NOT NULL default '0',
  media_id int(11) NOT NULL default '0',
  PRIMARY KEY  (file_id,media_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkFileToOperatingSystem`
--

CREATE TABLE LinkFileToOperatingSystem (
  file_id int(11) NOT NULL default '0',
  operating_system_id int(11) NOT NULL default '0',
  PRIMARY KEY  (file_id,operating_system_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkFileToPathSHA`
--

CREATE TABLE LinkFileToPathSHA (
  file_id int(11) NOT NULL default '0',
  path_sha_id int(11) NOT NULL default '0',
  PRIMARY KEY  (file_id,path_sha_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkManufacturerToManufacturer`
--

CREATE TABLE LinkManufacturerToManufacturer (
  parent_manufacturer_id int(11) NOT NULL default '0',
  child_manufacturer_id int(11) NOT NULL default '0',
  PRIMARY KEY  (parent_manufacturer_id,child_manufacturer_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkMediaToApplication`
--

CREATE TABLE LinkMediaToApplication (
  media_id int(11) NOT NULL default '0',
  application_id int(11) NOT NULL default '0',
  PRIMARY KEY  (media_id,application_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkMediaToMediaType`
--

CREATE TABLE LinkMediaToMediaType (
  media_id int(11) NOT NULL default '0',
  media_type_id int(11) NOT NULL default '0',
  PRIMARY KEY  (media_id,media_type_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkOperatingSystemToManufacturer`
--

CREATE TABLE LinkOperatingSystemToManufacturer (
  operating_system_id int(11) NOT NULL default '0',
  manufacturer_id int(11) NOT NULL default '0',
  PRIMARY KEY  (operating_system_id,manufacturer_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkOperatingSystemToPlatform`
--

CREATE TABLE LinkOperatingSystemToPlatform (
  operating_system_id int(11) NOT NULL default '0',
  platform_id int(11) NOT NULL default '0',
  PRIMARY KEY  (operating_system_id,platform_id)
) TYPE=MyISAM;

--
-- Table structure for table `LinkPackageToOperatingSystem`
--

CREATE TABLE LinkPackageToOperatingSystem (
  package_id int(11) NOT NULL default '0',
  operating_system_id int(11) NOT NULL default '0',
  PRIMARY KEY  (package_id,operating_system_id)
) TYPE=MyISAM;

--
-- Table structure for table `Manufacturer`
--

CREATE TABLE Manufacturer (
  id int(11) NOT NULL auto_increment,
  code varchar(15) NOT NULL default '',
  name varchar(150) NOT NULL default '',
  address1 varchar(50) default NULL,
  address2 varchar(50) default NULL,
  city varchar(50) default NULL,
  stateprov varchar(50) default NULL,
  postal_code varchar(10) default NULL,
  country varchar(50) default NULL,
  creation_date timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY code (code)
) TYPE=MyISAM;

--
-- Table structure for table `Media`
--

CREATE TABLE Media (
  id int(11) NOT NULL auto_increment,
  name varchar(50) NOT NULL default '',
  comment varchar(250) default NULL,
  globalization char(3) default NULL,
  creation_date timestamp(14) NOT NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

--
-- Table structure for table `MediaType`
--

CREATE TABLE MediaType (
  id int(11) NOT NULL auto_increment,
  type varchar(10) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY type (type)
) TYPE=MyISAM;

--
-- Table structure for table `OperatingSystem`
--

CREATE TABLE OperatingSystem (
  id int(11) NOT NULL auto_increment,
  code varchar(15) NOT NULL default '',
  name varchar(150) NOT NULL default '',
  version varchar(15) NOT NULL default '',
  comment varchar(250) default NULL,
  creation_date timestamp(14) NOT NULL,
  general_code varchar(15) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY oscode (code)
) TYPE=MyISAM;

--
-- Table structure for table `PathSHA`
--

CREATE TABLE PathSHA (
  id int(11) NOT NULL auto_increment,
  sha varchar(40) NOT NULL default '',
  path varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY path (path),
  UNIQUE KEY sha (sha)
) TYPE=MyISAM;

--
-- Table structure for table `Platform`
--

CREATE TABLE Platform (
  id int(11) NOT NULL auto_increment,
  name varchar(150) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY name (name)
) TYPE=MyISAM;

--
-- Table structure for table `Staff`
--

CREATE TABLE Staff (
  id int(11) NOT NULL auto_increment,
  code varchar(15) NOT NULL default '',
  project_role varchar(15) NOT NULL default '',
  first_name varchar(50) NOT NULL default '',
  middle_name varchar(50) default NULL,
  last_name varchar(50) NOT NULL default '',
  telephone varchar(40) default NULL,
  email varchar(80) default NULL,
  comment varchar(250) default NULL,
  active varchar(10) NOT NULL default 'Yes',
  administrator varchar(10) NOT NULL default 'No',
  hash_permissions varchar(50) NOT NULL default 'No',
  password varchar(50) NOT NULL default '',
  creation_date timestamp(14) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY code (code)
) TYPE=MyISAM;