help me think this structure through

[mbeals]

I'm designing a database to hold IP assignment data.  It will hold information about the circuit, NAT translation mappings (public -> private address) and info about the attached device.

 

I have three main classes of device: CMTS, provisioning server and others.  The CMTS and Provisioning Servers have unique attributes that make it worthwhile tracking them in their own tables.

 

The structure is as follows

 

CREATE TABLE `circuits` (
  `index` int(11) NOT NULL auto_increment,
  `prop` varchar(30) NOT NULL,
  `priority` int(2) NOT NULL,
  `type` varchar(20) NOT NULL,
  `up` varchar(10) NOT NULL,
  `down` varchar(10) NOT NULL,
  `dns1` varchar(20) NOT NULL,
  `dns2` varchar(20) NOT NULL,
  `lanIP` varchar(20) NOT NULL,
  `maskbits` int(2) NOT NULL,
  `gateway` varchar(20) NOT NULL,
  `CID` varchar(20) NOT NULL,
  `serial` varchar(20) NOT NULL,
  `smask` varchar(20) NOT NULL,
  `comments` longtext NOT NULL,
    PRIMARY KEY  (`index`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `CMTS` (
  `index` int(11) NOT NULL auto_increment,
  `publicIP` varchar(20) NOT NULL,
  `localIP` varchar(20) NOT NULL,
  `snmp` int(11) NOT NULL,
  `circuitID` int(11) NOT NULL,
  `group` int(11) NOT NULL,
  `webadmin` int(11) NOT NULL,
  `remote_storage` int(11) NOT NULL,
  `pass` varchar(20) NOT NULL,
  PRIMARY KEY  (`index`),
  UNIQUE KEY `publicIP` (`publicIP`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `Provservers` (
  `index` int(11) NOT NULL auto_increment,
  `publicIP` varchar(20) NOT NULL,
  `localIP` varchar(20) NOT NULL,
  `circuitID` int(11) NOT NULL,
  `pass` varchar(20) NOT NULL,
  PRIMARY KEY  (`index`),
  UNIQUE KEY `publicIP` (`publicIP`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;


CREATE TABLE `netdevices` (
  `index` int(5) NOT NULL auto_increment,
  `circuitID` varchar(30) NOT NULL,
  `publicIP` varchar(20) NOT NULL,
  `localIP` varchar(40) NOT NULL,
  `admin` varchar(10) NOT NULL,
  `test` tinyint(1) NOT NULL default '0',
  `status` varchar(20) NOT NULL,
  `use` longtext NOT NULL,
  `group` int(2) NOT NULL default '2',
  `pass` varchar(20) NOT NULL,
  `comments` longtext NOT NULL,
  PRIMARY KEY  (`index`),
  KEY `prop` (`circuitID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

I need to to keep this modular, such that I can add devices later that may have specialized attributes.  My problem in handling it this way is that I now have no common index for all devices.  When I build a table of all devices on a circuit (using Unions ), I cannot identify each record uniquely by a single index value (making updating a PITA).  Any thoughts on how to proceeded?