The database structure is kept as simple as possible at the moment. In the future there will most certainly be extensions for access control and history mechanisms though.
There are 6 tables for the relevant information. One of them is the Job table which is used to log all activities and to decouple user-interaction from job execution. Four tables represent the business entities Server, Zone, Label and Record and the last one is used to represent the relationship between multiple zones on multiple servers.
The idea is that a Zone consists of multiple Labels, one of which has the symbolic name '@' and represents the zone itself. So the Zone object is only a container for Labels. Each Label, on the other hand, consists of multiple Records which means that the Label is a container for Records. This leads to an object hierarchie in a tree structure with the specific Zone at the top and the records as leave nodes.
The name servers are represented by Server entities. They are also containers for Zones but one Zone can be assigned to multiple servers. One of these Servers must be the primary name server (even if it is not managed by ZoneMaster) and therefore all the others are slaves.
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| id | int(8) | | PRI | NULL | auto_increment |
| name | char(80) | | MUL | | |
| managed | enum('N','Y') | | MUL | N | |
+---------+---------------+------+-----+---------+----------------+
This table contains the names of all known servers. They can be known because they are managed by ZoneMaster or they can be known because they are an authoritative master of a zone. The enum filed 'managed' is used to distinguish between those two cases.
+--------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------+------+-----+---------+-------+
| zone | int(8) | | PRI | 0 | |
| server | int(8) | | PRI | 0 | |
+--------+--------+------+-----+---------+-------+
This table simply connects multiple zones with multiple servers as one zone can be actively configured on multiple name servers. One of these servers is the master server but this information is available in the Zone table.
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(8) | | PRI | NULL | auto_increment |
| state | enum('N','O') | | MUL | N | |
| name | char(80) | | MUL | | |
| pid | int(8) | | MUL | 0 | |
+-------+---------------+------+-----+---------+----------------+
This table contains the names of all zones an a reference to their authoritative master name server. The field pid (parent id) contains this reference. The enum field 'state' is used to mark outdated entries. This is required for the job queuing mechanism and the history. No entry is really deleted from the database but marked as outdated. Because of the small amount of data, compared with some multimedia databases, this shouldn't lead to a problem even for large name servers as modifications normally don't take place frequently. For future versions there is the idea to implement an archiving mechanism which might limit the growth of the database.
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(8) | | PRI | NULL | auto_increment |
| state | enum('N','O') | | MUL | N | |
| name | char(80) | | MUL | | |
| pid | int(8) | | MUL | 0 | |
+-------+---------------+------+-----+---------+----------------+
This table contains the names of all labels as well as the state as describe above. The parent ID (pid) is a reference to the zone which the label belongs to.
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| id | int(8) | | PRI | NULL | auto_increment |
| state | enum('N','O') | | MUL | N | |
| type | char(15) | | MUL | | |
| ttl | int(8) | | | 0 | |
| content | char(120) | | | | |
| pid | int(8) | | MUL | 0 | |
+---------+---------------+------+-----+---------+----------------+
This table contains the record data (field named content as data is a reserved word). The TTL field contains the time-to-live of this record and the type is something like 'IN A', 'IN MX' and so on. This means that the type in this context represents a combination of the 'class' and the 'type' as described in the relevant Request For Comments (RFC) for simplicity reasons. This parent ID (pid) in this case is the Label to which the Record belongs.
+--------+-------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+----------------+
| id | int(8) | | PRI | NULL | auto_increment |
| name | enum('Zone','Label','Record') | | | Zone | |
| type | enum('create','delete') | | | create | |
| this | int(8) | | | 0 | |
| status | char(10) | | MUL | TODO | |
+--------+-------------------------------+------+-----+---------+----------------+
This table contains job information. A job is either a create or a delete job which is what the type field is for. The name field refers to one of the business entity types, i.e. one of the tables a job can refer to. The this field is the relevant ID and the status field is used to distinguish between jobs that are already done and the others.
If for example a Record has to be added to a label the entry in inserted into
the database and a 'create' job for a 'Record' with the relevant ID is added.
The application can therefore identify the record as being in the state of creation.
This process of creation is finished when the status field of the job is changed
to something different than 'TODO'.
The job handling mechanism might be extended to allow backtracking of all changes and to provide predated actions in the future. The idea is to leave completed jobs in the database which provides the possibility to reconstruct all changes and therefore to undo them. Undoing changes should be new jobs and therefore no job information is deleted at all.
Predating a job could be realized through a timestamp. The library could provide the possibility to set this timestamp as an absolute or relative value. In both of these cases an absolut timestamp is saved in the database. Due to the lack of triggers in MySQL this only works if the scheduler is running on a regular basis.
Another idea for future releases is to provide additional information in the database as the creation timestamp, the responsible user and things like that. The database structure therefore has to be expanded in the future.