DNS Monitoring Database Schema

CREATE TABLE Hosts
(
	ID NUMERIC(10,0) IDENTITY,
	Name VARCHAR(255) NOT NULL,
	LastChecked DATETIME NULL,

	CONSTRAINT PK_ID_Hosts PRIMARY KEY CLUSTERED (ID),
	CONSTRAINT UQ_Name_Hosts UNIQUE (Name)
)

CREATE TABLE IPs
(
	ID NUMERIC(10,0) IDENTITY,
	IP INT NOT NULL,

	CONSTRAINT PK_ID_IPs PRIMARY KEY CLUSTERED (ID),
	CONSTRAINT UQ_IP_IPs UNIQUE (IP)
)

CREATE TABLE Mappings
(
	ID NUMERIC(10,0) IDENTITY,
	HNID INT NOT NULL,
	IPID INT NOT NULL,
	LastSeen DATETIME NOT NULL,

	CONSTRAINT PK_ID_Mappings PRIMARY KEY CLUSTERED (ID),
	CONSTRAINT FK_HNID_Mappings FOREIGN KEY (HNID) REFERENCES Hosts(ID),
	CONSTRAINT FK_IPID_Mappings FOREIGN KEY (IPID) REFERENCES IPs(ID),
	CONSTRAINT UQ_Mappings UNIQUE (HNID,IPID)
)