Ant Hill Schema

Below is a list of all tables contained within the schema followed by a short explanation

tld

CREATE TABLE tld (
    tid serial unique NOT NULL,
    extension character varying(20) NOT NULL
);

ALTER TABLE ONLY tld ADD CONSTRAINT tld_pkey PRIMARY KEY (tid);
CREATE UNIQUE INDEX tld_extension_index ON tld USING btree (extension);

This table is merely a list of possible top level domain extensions. The zone names were broken apart to allow a programmer to link prices and/or policies to a certain extension.

zone

CREATE TABLE "zone" (
    zid serial unique NOT NULL,
    name character varying(255) NOT NULL,
    tld integer NOT NULL,
    comment text DEFAULT ''
);

ALTER TABLE ONLY "zone" ADD CONSTRAINT zone_id_pkey PRIMARY KEY (zid);
CREATE UNIQUE INDEX zone_name_tld_index ON "zone" USING btree (name, tld);
ALTER TABLE ONLY "zone" ADD CONSTRAINT "$1" FOREIGN KEY (tld) REFERENCES tld(tid);

This table obviously houses all registered domains on your primary name server. Considering the last paragraph this table only contains the name without the tld. A foreign key references to the tld. To ensure data integrity all zone names will be removed if their respective tld is deleted.

record

CREATE TABLE record (
    rid serial unique NOT NULL,
    host character varying(255) DEFAULT '@'::character varying,
    "zone" integer NOT NULL,
    ttl integer DEFAULT 21600,
    "type" character varying(5),
    priority character varying(5) DEFAULT ''::character varying NOT NULL,
    data text NOT NULL,
    created timestamp with time zone DEFAULT now(),
    updated timestamp with time zone DEFAULT now(),
    CONSTRAINT record_type CHECK (((((((((((((("type")::text = 'A'::text) OR 
	(("type")::text = 'AAAA'::text)) OR (("type")::text = 'CNAME'::text)) 
	OR (("type")::text = 'HINFO'::text)) OR (("type")::text = 'MBOXFW'::text)) 
	OR (("type")::text = 'MX'::text)) OR (("type")::text = 'NAPTR'::text)) 
	OR (("type")::text = 'NS'::text)) OR (("type")::text = 'PTR'::text)) 
	OR (("type")::text = 'SOA'::text)) OR (("type")::text = 'TXT'::text)) 
	OR (("type")::text = 'URL'::text)))
);

ALTER TABLE ONLY record ADD CONSTRAINT record_pkey PRIMARY KEY (rid);

CREATE INDEX record_type_index ON record USING btree ("type");
CREATE INDEX record_host_zone_index ON record USING btree (host, "zone");

ALTER TABLE ONLY record ADD CONSTRAINT "$1" FOREIGN KEY ("zone") 
    REFERENCES "zone"(zid) ON DELETE CASCADE;		

This table is made up of the real dns data. It contains the hostnames and the associated data. A foreign key references the associated zone. The constraint from the last passage also applies here. If a zone is removed the associated records will be removed as well.

canonical

CREATE TABLE canonical (
    cid serial unique NOT NULL,
    "domain" integer NOT NULL,
    content text NOT NULL,
    FOREIGN KEY ("domain") REFERENCES "zone"(zid) ON DELETE CASCADE
);

ALTER TABLE ONLY canonical ADD CONSTRAINT canonical_pkey PRIMARY KEY (cid);

CREATE UNIQUE INDEX canonical_content_index ON canonical USING btree (content);

Originally this table wasn't present in the schema. The data contained within this table actually causes data redundancy. However, one of the primary demands for a name server is query speed. It is far less efficient to concatenate a tld and a name in a WHERE-clause than to have a table with an already concatenated zone name.

xfr

CREATE TABLE xfr (
    xid serial unique NOT NULL,
    "zone" integer NOT NULL,
    client cidr NOT NULL,
    FOREIGN KEY ("zone") REFERENCES "zone"(zid) ON DELETE CASCADE
);

ALTER TABLE ONLY xfr ADD CONSTRAINT xfr_pkey PRIMARY KEY (xid);

CREATE UNIQUE INDEX xfr_zone_client_index ON xfr USING btree ("zone", client);

This table lists the allowed transfers of zones for certain clients. This table will allow a customer to specify a separate name server to run as a secondary for a domain.