IP Register database table structure

General features

The IP address relational database consists of a number of tables, many of which are interconnected by referential constraints. There are broadly three classes of table.

Firstly, anything that can be conceived of as an object in the real world of the network is represented by a row in a table that simply lists such objects. For example, we have the concept of a box, and there is a table BOX that lists all boxes, along with their individual private properties such as location and equipment type. Not all objects have such concrete aspect as boxes. An IP address for example is an object, not least because in the v4 world it is a distinct and valuable non-wasting resource. Some objects are really classifiers, all in the mind as it were, as with mzones, which represent spheres of managerial authority. Some are associations, as in MZONE_CO. Nevertheless, these all have individual front-line tables which suitably privileged people can inspect and update. All of these tables standardly have columns updated_by, update_date, remarks and review_date. Updated_by and update_date are set automatically. Remarks and review_date are left to the discretion of the data maintainer, be that an institutional CO or a CS registrar. Remarks is plain text and intended for human inspection, whereas review_date is of type date and suitable therefore for SELECTion say with where sysdate>=review_date.

Secondly, there are history tables. These are maintained automatically and trace the histories of rows in the main object tables. They are there solely to provide an audit trail. They can be read but not (overtly) written. Whenever a main table is updated, a copy of the entire row as it was previously is inserted into the corresponding history table. The trail of updates on any individual row is linked by an automatically supplied thread identifier.

Thirdly, there are a number of tables which are maintained automatically and intended to remain behind the scenes. The purpose of these is to bolster the self-consistency of the database through being the subjects of various uniqueness or referential constraints. You should only become aware of these if you attempt to introduce some inconsistency into the database. Unfortunately you are likely to get a rather horrible error message if you attempt to violate a constraint, but you should not be dismayed by this, rather read it carefully and apply intelligence to the carefully chosen constraint name that is buried in the message.

All of these tables have global scope, and there are no dynamically created tables, nor even tables created by the CS per institution, say. Absolutely all boxes, for example, are collected together in the one and only table BOX. Restricted access is delivered by means of views. Even these views are statically created, once and for all time, but they select dynamically depending on the enquirer. For example, for user fred the view MY_BOX will select just those boxes belonging to mzones for which fred is a Computer Officer. The view MY_whatever is granted to PUBLIC, and the underlying table is granted to no-one. The access policy is thus encapsulated and dynamically evaluated by the view. The grants allow reading of all fields (of revealed rows), but insertion, update and deletion are variously restricted. A CO can for example modify a v4_address to bring it into use or retire it, but can neither create nor delete one.

Every attempt has been made to render the database robust and secure, but you are not invited to try to break it, except in pure thought. It is an important operational enterprise and access to it is given only to those prepared to treat it responsibly.

The remainder of this document discusses the principal object types. You might like to refer in parallel to the table and data definitions.


A box is pretty much what you might expect, a computer, a printer, a door lock, any physical hardware object that is connected to the CUDN. Don't go trying to create mythical virtual boxes; separate apparatus is provided for that. A box is identified by a name, a fully qualified domain name, that is. Its attributes include the manner of thing that it is (PC, Mac, Coffee Pot or whatever), its location (room & building), its owner (individual, "dept" or "College"), its system manager (individual or "CO") and its end user (optional, but useful to aid identification for example where it is some particular clerk or technician). You can and should create and destroy box entries as and when equipment comes and goes, as well of course as updating them as appropriate. You can, incidentally, keep a box on record even when it has no IP address associated, though this does tie down the name.


A vbox is something that from the point of view of the network is more or less indistinguishable from a real box, but is in fact a virtual box, an alter ego of some ordinary physical box that hosts it but otherwise has it at arm's length. It is not the concept to use for a box that is an integrated whole but has multiple interfaces with differing characteristics or purposes, for which ANAMEs are normally more suitable. Nor is it suitable as a migration aid or simply as a means of hiding a service location behind a distinct service name, for which CNAMEs or ANAMEs are more suitable. A case where it probably would be sensible is a non-stop critical service that can failover from one machine to another, the address being grabbed dynamically at arp level.


IPv4 addresses are a valuable and scarce commodity. They have a permanent representation in the database, whether or not they are actually in use at any particular time. The table entries are created by CS registrars, and update by institutional COs is severely limited. Addresses are tagged with the mzone to which they belong. COs can mark them as being in use or out of use. When in use, they are linked to the associated box or vbox by virtue of having the same name field value. The status field specifies whether they are in use and if so whether on a box or a vbox.

A box can be associated with several addresses, as is normal in the case of routers. The DNS will map all the addresses into the one box name, and the map the box name into the whole collection of addresses. See also ANAMEs.


An mzone represents a sphere of authority and is identified by a short text name assigned by the CS registrars, which will probably match the main or only domain associated with the mzone, for example BOTOLPH. Mzones cannot overlap, and an address is associated (by the CS) with precisely one. This association is what limits the assignment of addresses by COs, who can only deal with addresses in mzones with which they are personally associated. Addresses are individually associated with mzones, and subnets are an entirely orthogonal concept. Thus there is scope for sharing of subnets where and an mzone may span several subnets.

A CO can be associated with one or more mzones by virtue of entries in the table MZONE_CO. COs who work for more than one institution will have to be careful in the exercise of their authority. This is the principal means by which authority is delegated to COs.


DNS domains are accorded by the CS registrars, and a domain is associated with precisely one mzone. This limits the names that can be used within an mzone and the association between name and mzone defines authority in respect of boxes.


An aname is a named alternate address for a box or vbox. It will normally be a service name, perhaps defining the address corresponding to a virtually hosted web site. The DNS maps the name to that one address, but does still map the address back to the box name rather than the aname name. This is normally appropriate. In the rare case where this is not appropriate a vbox will have to be used.


A cname is simply a textual alias name, normally for a box or vbox name. It corresponds precisely with a DNS CNAME RR.


The lan is the concept to fasten on as a CO, not the subnet. The lan apparatus is provided purely for the benefit of COs, to help in the assignment of addresses. A lan is simply an aggregator of addresses. In some respects it is comparable with an mzone, but COs can create and destroy lans and can associate addresses with them autonomously. They have no significance to the CS, except where management is not (yet) devolved and is (still) being undertaken by the CS. Thus for example, you might tag one collection of addresses as belonging to the admin lan and the rest to the academic lan. Maybe you would have a separate lan for some remote hostel. Maybe you have just the one lan for the whole institution. It's up to you the local CO. The point of course is that you sometimes do need to partition your addresses in these sorts of ways, perhaps because of subnet routing constraints, perhaps because of MAC level routing. The apparatus enables you to separate the process of assigning routes and network partitions from the business of issuing addresses. It all comes together because at the end you can select new addresses by some predicate such as where lan='ADMIN'.

V4_subnets, on the other hand are very much under the control of the CS, since private IP routing is strictly regulated. An address belongs to precisely one subnet, and the subnet record includes environmental detail such as netmask and router addresss. You may choose to have your lans match your subnets, but there is no inherent connection so far as the database is concerned.

LANs and nameservers

Not withstanding the remarks above, about LANs being entirely for the benefit of COs, we have experimentally taken advantage of the apparatus to associate recommended nameserver addresses with LANs. This is purely an advisory feature, but is quite convenient, allowing automatic inclusion of nameserver information in the delivery of other environmental data, such as is delivered with a new address assignment. The table LAN_NS4 associates (IPv4) nameserver addresses (with preference numbers) with particular LANs.