-- Here is my table structure for keeping my DNS data in MySQL.
-- It is important to note that the FQDN column in the PTR table requires
-- a trailing '.' unlike the other tables.

-- MySQL dump 9.11
--
-- Host: asylum    Database: DNS
-- ------------------------------------------------------
-- Server version	4.0.22

--
-- Table structure for table `A`
--

CREATE TABLE A (
  RecordID smallint(5) unsigned NOT NULL auto_increment,
  FQDN varchar(255) NOT NULL default '',
  IP varchar(15) NOT NULL default '',
  TTL smallint(5) unsigned NOT NULL default '0',
  Location char(2) NOT NULL default '',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY ARecord (FQDN,IP,Location)
) TYPE=MyISAM;

--
-- Dumping data for table `A`
--


--
-- Table structure for table `CNAME`
--

CREATE TABLE CNAME (
  RecordID tinyint(3) unsigned NOT NULL auto_increment,
  SourceFQDN varchar(200) NOT NULL default '',
  DestFQDN varchar(200) NOT NULL default '',
  TTL smallint(5) unsigned NOT NULL default '0',
  Location char(2) NOT NULL default '',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY CNAMERecord (SourceFQDN,DestFQDN,Location)
) TYPE=MyISAM;

--
-- Dumping data for table `CNAME`
--


--
-- Table structure for table `Location`
--

CREATE TABLE Location (
  LocationID tinyint(3) unsigned NOT NULL auto_increment,
  Location char(2) NOT NULL default '',
  IP varchar(15) NOT NULL default '',
  PRIMARY KEY  (LocationID),
  UNIQUE KEY Location (Location,IP)
) TYPE=MyISAM;

--
-- Dumping data for table `Location`
--


--
-- Table structure for table `MX`
--

CREATE TABLE MX (
  RecordID tinyint(3) unsigned NOT NULL auto_increment,
  FQDN varchar(200) NOT NULL default '',
  IP varchar(15) NOT NULL default '',
  ServerFQDN varchar(200) NOT NULL default '',
  Distance enum('5','10','15','20','25','30','35','40','45','50','55','60','65','70','75','80','85','90','95') NOT NULL default '5',
  TTL smallint(5) unsigned NOT NULL default '0',
  Location char(2) NOT NULL default '',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY MXRecord (FQDN,Distance)
) TYPE=MyISAM;

--
-- Dumping data for table `MX`
--


--
-- Table structure for table `NS`
--

CREATE TABLE NS (
  RecordID tinyint(3) unsigned NOT NULL auto_increment,
  Domain varchar(127) NOT NULL default '',
  IP varchar(15) NOT NULL default '',
  ServerFQDN varchar(255) NOT NULL default '',
  TTL smallint(5) unsigned NOT NULL default '0',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY DomainServer (Domain,ServerFQDN)
) TYPE=MyISAM;

--
-- Dumping data for table `NS`
--


--
-- Table structure for table `PTR`
--

CREATE TABLE PTR (
  RecordID tinyint(3) unsigned NOT NULL auto_increment,
  ArpaFQDN varchar(200) NOT NULL default '',
  FQDN varchar(200) NOT NULL default '',
  TTL smallint(5) unsigned NOT NULL default '0',
  Location char(2) NOT NULL default '',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY PTRRecord (ArpaFQDN,Location)
) TYPE=MyISAM;

--
-- Dumping data for table `PTR`
--


--
-- Table structure for table `SOA`
--

CREATE TABLE SOA (
  RecordID tinyint(3) unsigned NOT NULL auto_increment,
  Domain varchar(200) NOT NULL default '',
  DNSServerFQDN varchar(200) NOT NULL default '',
  ContactEmail varchar(200) NOT NULL default '',
  SerialNumber int(10) unsigned NOT NULL default '0',
  RefreshTime int(10) unsigned NOT NULL default '0',
  RetryTime int(10) unsigned NOT NULL default '0',
  ExpireTime int(10) unsigned NOT NULL default '0',
  MinimumTime int(10) unsigned NOT NULL default '0',
  TTL smallint(5) unsigned NOT NULL default '0',
  Location char(2) NOT NULL default '',
  Active enum('Y','N') NOT NULL default 'Y',
  Comment varchar(255) NOT NULL default '',
  PRIMARY KEY  (RecordID),
  UNIQUE KEY SOARecord (Domain)
) TYPE=MyISAM;

--
-- Dumping data for table `SOA`
--


