[fzzzt]

Hi all,

I am trying to create a server database that holds information about (duh) servers. It will hold info like IPs, domains, and software versions. Basically this database serves one major purpose: when a new software version is released, I need to be able to query the db and find out which servers need upgrading. I need to be able to add servers and software at will and it needs to be modular so I can add things later (users, etc.). Basically I'm trying not to hard-code too much stuff into a single table, which I guess is good normalization anyways. I have a couple problems I'm not sure what to do about. My first problem is designing the whole "domain <-> IP <-> server" relationship(s):

* domain N-1 ip
* ip N-1 server

+-------------------------+
|         domains         |
+----------+--------+-----+
| hostname | domain | tld |
+----------+--------+-----+

+--------------------+
|         ip         |
+-------+------------+
| ip_id | ip_address |
+-------+------------+

+-------------------------+
|         server          |
+-----------+----+--------+
| server_id | os | kernel |
+-----------+----+--------+

+-------------------+
|        dns        |
+-----------+-------+
| domain_id | ip_id |
+-----------+-------+

+-------------------+
|     addresses     |
+-----------+-------+
| server_id | ip_id |
+-----------+-------+

My second problem is that I need to store two different sets of version information for each piece of software: the latest version and the version that is installed in each server. Some servers won't be the latest version, for various reasons (incompatible hardware, haven't had time yet, etc.). The "installed_software" table will have lots of redundant data, though. I have a list of about 20 software packages to track, which will probably grow. With 30 servers, 20 software each, that is 600 table entries. Is there a better way to do this? I can't think of one...

* software N-N server

+-------------------------------------+
|              software               |
+-------------+------+----------------+
| software_id | name | latest_version |
+-------------+------+----------------+

+-----------------------------------+
|        installed_software         |
+-----------+-------------+---------+
| server_id | software_id | version |
+-----------+-------------+---------+

Any concerns/comments/suggestions on how I am designing this? I guess my main question is are there better ways to organize this? I'm obviously not a pro, I think this is the third database I've designed, so any input is welcome. Sorry for the beautiful ascii art hehe.

Josh

[barryw]

[quote][size=1]Originally posted by fzzzt
Hi all,

I am trying to create a server database that holds information about (duh) servers. It will hold info like IPs, domains, and software versions.

Here's a Data Model that might help :-
http://www.databaseanswers.com/data_...ming/index.htm

It does not include Domains so the first step would be to enhance the Business Rules so that the Data Model can be enhanced accordingly :-
http://www.databaseanswers.com/data_...ming/facts.htm

Then review whether my definition of a Server is the same as yours.

Barry Williams
Principal Consultant
Database Answers