The Network People Support Forums

I am in the process of setting up nictool in my environment which consists of the following workflow:

nictool export data ->opendnssec->nameservers

By setting up the workflow this way, I allow opendnssec to do all of my keymanagement in a fairly automated method. What I need to do in the future is add ds records to the zone since I have secure delegations in my zone.

The mysql table for the zone records currently looks something like:

CREATE TABLE nt_zone_record(
    nt_zone_record_id   INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    nt_zone_id          INT UNSIGNED NOT NULL,
    name                VARCHAR(255) NOT NULL,
    ttl                 INT UNSIGNED NOT NULL DEFAULT '0',
    description         VARCHAR(255),
    type                ENUM('A','AAAA','MX','PTR','NS','TXT','CNAME','SRV'),
    address             VARCHAR(255) NOT NULL,
    weight              SMALLINT UNSIGNED,
    priority            SMALLINT UNSIGNED,
    other               VARCHAR(255),
    deleted             ENUM('0','1') DEFAULT '0' NOT NULL
);

Since the ds records consist of the following items:

keytag
algorithm number
Digest type
Digest

Would it be better to create these additional fields as seperate columns in the database or would you rather reuse some of the other fields?  Since disk space is cheap, I was leaning toward creating new columns which would help keep the database schema less confusing.

Steve

I'd lean towards putting the dnssec data into a related table. Odds are good that dnssec will be replaced someday, and I'd rather not have the implementation baggage tied up in the zone record schema. Having it in the code will be bad enough.

Also, having the dnssec data in a related table won't use disk space for zones/records that have no dnssec data.

Does this really pay to put in a separate table?  The DS record does not really do much for DNSSEC, but rather for delegations.  By moving it to a separate table, the logging/undelete become more complicated. 

Also, if most of the additional fields are VARCHAR/SMALLINT, it really add much to the size of the table.

The one thing a separate table would help with would be to have the database itself try to enforce some of the data integrity.

Steve

I honestly don't know if using a separate table will "pay," but my hunch is yes. Sure, it complicates logging and undelete, but to my memory, that's just a matter of updating a few queries with JOINs.

The disk space isn't an issue for most NicTool installs. On my NicTool server, the disk space is 226K. But for the installs that have millions of RRs, that table is a huge, so a 15 or 20% increase in table size can make a substantial difference.

I leave it to you to decide.

You might be pleased to see this:

CREATE TABLE `resource_record_type` (
  `id` smallint(2) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO resource_record_type VALUES (2,'NS'),(5,'CNAME'),(6,'SOA'),(12,'PTR'),(15,'MX'),(28,'AAAA'),(33,'SRV'),(99,'SPF'),(252,'AXFR'),(1,'A'),(16,'TXT'),(48,'DNSKEY'),(43,'DS'),(25,'KEY');

Now, exactly which 4 records types do you need?  The names you provided don't easily match up with the IETF defined record types. I've guessed at least 25,43,48.  But there's 10 different DNSSEC related resource record types in various stages (proposed, standards track).

Having given your initial question (new or reuse fields?) more though, and a thorough refresh of the NicToolServer code, I think it's best to reuse the existing DNS record fields. It causes the least amount of pain and suffering.