Database schema

This is developed on Centos 6.x using the MySQL supplied with the distribution. The should be no specific features used dependent on minor release versions.

Although this uses MySQL at present, I would like it to be written to be portable to other databases with minimal effort.

Tables used

mysql> show tables;
+---------------+
| Tables_in_DNS |
+---------------+
| DOMAINNAME    |
| FORWARDZONE   |
| MEMBERS       |
| TEAMMEMBERS   |
| TEAMS         |
| USERS         |
| ZONEINFO      |
+---------------+

DOMANNAME table contains the domain names this project can manage.
FORWARDZONE contains all the A, CNAME, MX, TXT, AAAA etc records.
MEMBERS describes the relationship between TEAMMEMBERS and the TEAMs they are part of. Used to control access to change records.
TEAMMEMBERS contains the users - probably superceeded by USERS.
TEAMS contains the Desktop, Network, security and server team definitions.
USERS describes the allowed users and their details.
ZONEINFO is for the metadata at the top of a zone file, it may be better merged in to DOMAINNAME, or maybe DOMAINNAME should become part of ZONEINFO.

Table Descriptions

mysql> describe DOMAINNAME;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| domainsuffix | varchar(254) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
mysql> describe FORWARDZONE;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field        | Type         | Null | Key | Default           | Extra                       |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| hostname     | varchar(254) | NO   | PRI |                   |                             |
| domainsuffix | varchar(254) | YES  |     | NULL              |                             |
| description  | varchar(254) | YES  |     | NULL              |                             |
| username     | varchar(20)  | YES  |     | NULL              |                             |
| team         | varchar(20)  | YES  |     | NULL              |                             |
| modified     | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| recorddata   | varchar(40)  | YES  |     | NULL              |                             |
| recordclass  | varchar(2)   | YES  |     | NULL              |                             |
| recordtype   | varchar(5)   | YES  |     | NULL              |                             |
+--------------+--------------+------+-----+-------------------+-----------------------------+

Example data is:-

+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+
| hostname         | recordclass | recordtype | recorddata               | domainsuffix          | description                     |
+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+
| optiplex         | IN          | A          | 192.168.1.1              | rainsbrook.pri        | Optiplex in rack                |
| quince           | IN          | A          | 192.168.5.21             | rainsbrook.co.uk      | Quince in virtual server        |
| www.g1cwo.co.uk  | IN          | CNAME      | quince.rainsbrook.co.uk. | g1cwo.co.uk           | Website                         |
| newname          | IN          | CNAME      | oldname                  | rainsbrook.pri        | Alias from old server to new    |
+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+

Mapping of records in to FORWARDZONE.

hostname recordclass recordtype recorddata
hostname IN A ipaddress
hostname IN AAAA ipaddress
hostname IN CNAME hostname (no '.')
hostname IN CNAME fqdn. (with '.')
mysql> describe USERS;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| username     | varchar(20)  | NO   |     | NULL    |       |
| password     | varchar(20)  | NO   |     | NULL    |       |
| fullname     | varchar(254) | NO   |     | NULL    |       |
| emailaddress | varchar(254) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe MEMBERS;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username     | varchar(20) | NO   |     | NULL    |       |
| memberofteam | varchar(20) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe TEAMS;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| team         | varchar(20) | YES  |     | NULL    |       |
| teampassword | varchar(20) | YES  |     | NULL    |       |
| displayname  | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe TEAMMEMBERS;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username     | varchar(20) | YES  |     | NULL    |       |
| password     | char(25)    | NO   |     | NULL    |       |
| fullname     | varchar(40) | YES  |     | NULL    |       |
| emailaddress | varchar(40) | YES  |     | NULL    |       |
| memberof     | varchar(40) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
select users.password,
members.memberofteam,
teams.displayname from
users,
members,
teams
where
users.username='andrew' and
members.username=users.username and
teams.team=members.memberofteam;

Thanks to Brian Cadwell for this suggestion.

FIXME - decide on the storage engine to use:-

MYISAM:

MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB:

InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB

Currently all tables use MyISAM for no better reason than it seems to be the default:-

mysql> SELECT table_name, engine, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES order by CREATE_TIME;
+---------------------------------------+--------+---------------------+
| table_name                            | engine | CREATE_TIME         |
+---------------------------------------+--------+---------------------+
| DOMAINNAME                            | MyISAM | 2013-09-22 13:20:28 |
| MEMBERS                               | MyISAM | 2013-09-22 13:20:28 |
| USERS                                 | MyISAM | 2013-09-22 13:20:29 |
| TEAMS                                 | MyISAM | 2013-09-22 13:20:29 |
| TEAMMEMBERS                           | MyISAM | 2013-09-22 13:20:29 |
| ZONEINFO                              | MyISAM | 2013-09-22 13:20:30 |
| FORWARDZONE                           | MyISAM | 2014-03-19 09:28:38 |
| DELETEDFORWARDZONE                    | MyISAM | 2014-04-23 16:19:42 |
+---------------------------------------+--------+---------------------+

rb-projects-dns/dnsmgmtdbschema.txt · Last modified: 05/12/2014 12:21 by andrew