DB design question

[Anon]

Hi,

I was designed a database for our datacenter to keep track of the servers. I have designed four tables equipment, support pack, software and service level.

Here is my scenario.
1. Each of the equipment here could be a router, switch, a server or storage device.
2. Depending on the OS, some of the software are only applicable to one particular platform. For example esm field in the software table is only applicable to NT.
3. Each servers will many combination of service level. It could be a co-location service but with backup management or backup management combine with server management. Some servers may be just have bandwidth sharing but without server management.
4. Each server should have one support contract but in some case one support contract could include many servers.
5. I have even thought of related patch to each server but patches are also dependable on the platform.

I am getting very confused. Is this efficient for this kind of tables design. Please comment!

CREATE TABLE service(
service_id INT AUTO_INCREMENT,
service_desc CHAR(20),
PRIMARY KEY (service_id));

CREATE TABLE software(
soft_id INT AUTO_INCREMENT,
os CHAR(10),
pwplus CHAR(1),
ito_agent CHAR(1),
esm CHAR(1),
medusa CHAR(1),
measureware CHAR(1),
omni_media CHAR(1),
omni_disk CHAR(1),
managex CHAR(1),
PRIMARY KEY (soft_id));

CREATE TABLE support_pack(
support_id INT AUTO_INCREMENT,
support_pack_ref_no CHAR(10),
support_priority CHAR(1),
sys_handle_no CHAR(20),
support_start DATE,
support_end DATE,
PRIMARY KEY (support_id));

CREATE TABLE equipment(
equip_id INT AUTO_INCREMENT,
hostname CHAR(10),
serial_no CHAR(30),
model_no CHAR(20),
handover CHAR(1),
location CHAR(10),
rack_no CHAR(5),
equip_ip_address TEXT,
equip_desc TEXT,
vendor CHAR(10),
equip_type CHAR(10),
service_type CHAR(30),
support_id INT NOT NULL,
soft_id INT NOT NULL,
service_id INT NOT NULL,
FOREIGN KEY (service_id) REFERENCES service (service_id),
FOREIGN KEY (support_id) REFERENCES support_pack (support_id),
FOREIGN KEY (soft_id) REFERENCES software (soft_id),
PRIMARY KEY (equip_id));

[Anon]

First thing I'd do is break the software table apart into two pieces. One table that describes each software item (one row for each of the flags you currently have), and a link table that connects equipment to software (there could be several rows with the same equip_id that each point to a different soft_id). That gets rid of the array inside your software table and removes the link from your equipment table.

Then consider doing the same thing for the service and support_pack tables (a base table with a row for each distinct service or support_pack, and what I call an intersection table to link the equipment to zero or more service and support_pack rows). You could then remove the support_id and service_id links in the equipment table and have total flexibility as far as many-to-many relationships between equipment and software/service/support_pack.