#4 Improved data model for DNS database

[Stephen Cuppett]

I have made several modifications to the default
database schema used by MySQLBind to accurately
reflect the status of fields and provide more
information if you were to just look at the database
and get an idea of what was going on. Also, I have
added some foreign key constraints, etc on the tables
to minimize the amount of queries that need to happen
when things are modified and/or deleted. However,
these modifications cause breakage to the current CGI
GUI as well as mysqlBind in general when trying to
update zones. I would recommend implementing these
schema changes and/or additional changes as well as
updating the behavior of the app to work with and
take advantage of these schema settings. Some
elements of my schema require InnoDB tables which are
a standard part of the 4.0 series and were an addon
in the 3.23 series.

I have attached my schema.

In it I make a few assumptions based on my
preferences in database and DNS management. Such as
if the modby and/or createdby person is deleted, then
the field can now be NULL (instead of zero).
However, if the owner of a particular entry is zero,
that person's records will also be deleted. In the
event of an owner deletion, I would recommend jobs be
created for all DNS servers for all zones or the
effects a DELETE CASCADE will have may need to be
assessed by the program prior to deleting a person to
create the accurate jobs for the accurate zones.

Another change I made was that if a person who owns
and/or creates a job is deleted, those jobs are
deleted prior to running.

These foreign key constraints and column changes
provide a better data representation. As a last
resort, if the program wishes to work around the
constraints, it can also update the Owner field for
all referenced records to the person deleting the
present owner.

[Stephen Cuppett]

Recommended MySQLBind Schema

[Admin Role]

Logged In: YES
user_id=61124

Very cool! Thanks we will implement your code, and give you
full credit -of course- as long as you GPL license your
mods. If you can send a diff patch. Or want to -or have us-
create a branch on our cvs server, that would seem to be
even better: Like a mysql 4+ only version branch with InnoDB
tables etc. Any more development ideas welcome. Please feel
free to email us directly for nuts and bolts issues
regarding this request. support @ openisp . net

[Stephen Cuppett]

Logged In: YES
user_id=49270

I haven't made any modifications to the actual code base as
of yet, just the schema, but a 4.0+ branch that I could
commit to would be perfect. Just let me know what I need to do.

Presently, I am only utilizing mysqlBind and wishing to make
changes to that application.