[GerryKing]

I am working on a database that contains configuration management information about a client's servers. I will try to summarize with minimual data.

The database will contain a server resource record ( one record for each server [ Windows 2K, Unix ]) approximately 500 servers. This record will contain, hostname, ipaddress, hardware specifics, software and patches plus numerous other items quite volumous. Implementing this has not been a problem with several blobs and globs.
However, the client now wants the software packages and patches checked againt a vendors patch update database or security database.
Some of the servers contain hundreds of software packages ( Sun, Linux, FreeBSD, HP ) and thousands of patches. I'm torn between two approaches: 1) dump each blob and parse and create a temporary table with software and patch data as needed. Or 2) Create a table for each server with software and patch data. Option 2 seems more logical but violates the normalization rules. I would have 500 software tables and 500 patch tables but with data parsing only occuring once or only periodically based on updates.
Oh! Server records are updated weekly on the whole, with individual servers being updated on an as required basis. Validation reports can be run by different and multiple departments on an adhoc basis.

Thoughts! Comments! Hopefully suggestions

[andrewst]

Originally posted by GerryKing
I am working on a database that contains configuration management information about a client's servers. I will try to summarize with minimual data.

The database will contain a server resource record ( one record for each server [ Windows 2K, Unix ]) approximately 500 servers. This record will contain, hostname, ipaddress, hardware specifics, software and patches plus numerous other items quite volumous. Implementing this has not been a problem with several blobs and globs.
However, the client now wants the software packages and patches checked againt a vendors patch update database or security database.
Some of the servers contain hundreds of software packages ( Sun, Linux, FreeBSD, HP ) and thousands of patches. I'm torn between two approaches: 1) dump each blob and parse and create a temporary table with software and patch data as needed. Or 2) Create a table for each server with software and patch data. Option 2 seems more logical but violates the normalization rules. I would have 500 software tables and 500 patch tables but with data parsing only occuring once or only periodically based on updates.
Oh! Server records are updated weekly on the whole, with individual servers being updated on an as required basis. Validation reports can be run by different and multiple departments on an adhoc basis.

Thoughts! Comments! Hopefully suggestions

I'm not sure you should be using "blobs and globs" at all for this, since it obscures the data from being queried. Why not have a PACKAGES table and a PATCHES table with hundreds/thousands of rows in each?

Something like:

SERVERS( server_id PK, server_name, ... );

VENDORS( vendor_id PK, vendor_name, ... );

PACKAGES( package_id PK, package_name, vendor_id REFERENCES vendors, ... );

PATCHES( package_id REFERENCES packages, patch_id PK, ... );

SERVER_PACKAGES( server_id REFERENCES servers, package_id REFERENCES packages);

SERVER_PATCHES( server_id REFERENCES servers, patch_id REFERENCES patches );

This is just an outline, but you get the idea? Use a column per attribute and a row per item (package, patch, etc.) - don't stuff it all into some ghastly blob or glob!!!

[GerryKing]

Originally posted by andrewst
I'm not sure you should be using "blobs and globs" at all for this, since it obscures the data from being queried. Why not have a PACKAGES table and a PATCHES table with hundreds/thousands of rows in each?

Something like:

SERVERS( server_id PK, server_name, ... );

VENDORS( vendor_id PK, vendor_name, ... );

PACKAGES( package_id PK, package_name, vendor_id REFERENCES vendors, ... );

PATCHES( package_id REFERENCES packages, patch_id PK, ... );

SERVER_PACKAGES( server_id REFERENCES servers, package_id REFERENCES packages);

SERVER_PATCHES( server_id REFERENCES servers, patch_id REFERENCES patches );

This is just an outline, but you get the idea? Use a column per attribute and a row per item (package, patch, etc.) - don't stuff it all into some ghastly blob or glob!!!

Andrewst, thanks for the recommendation. I did think about that
structure but I ruled it out due do performance issues. I was basing my calculations upon an algorithim used in the contruction of an old ISAM Database Program. I had not allowed for use of an Index file to establish starting position in the data segment. I think I'm going to setup some bench mark datasets and tests -- just do satisfy my own curosity. Yes, I do agree about the blobs and globs. However, they
do provide a means for storing data to be dealt with later. I'm currently extracting the blobs and creating temporary tables for searches and queries ( extremely ineffecient but the data is available pending the final data format decisions and parsing routines). Like the old adage -- Hurry up and give me something now because we can always fix it later. But later never comes. We can never afford to do it right the first time but we can afford to redo it several times.