I designed a database for our data center to keep track of the servers. I designed four tables: equipment, support pack, software and service level.Here is my scenario.
- Each of the equipment here could be a router, switch, a server or storage device.
- Depending on the OS, some of the software is only applicable to one particular platform. For example esm field in the software table is only applicable to NT.
- Each server has many combinations of service level. It could be a co-location service but with backup management or backup management combined with server management. Some servers may just have bandwidth sharing but without server management.
- Each server should have one support contract, but in some cases one support contract could include many servers.
- I have even thought of related patches to each server, but patches are also dependable on the platform.
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));
The problem that seems to be confusing you (and me!) is that you are trying to make the tables in your schema serve more than one purpose. Each table should describe a single "thing" whether that is a server, or the relationship of patches to software or of software to server.
Create a table for each piece of hardware. Include only the information about that hardware (manufacturer, serial number, etc). Don't worry about the software that is on it, or the function(s) that the hardware provides in your network.
Create another table for services. Don't worry about what hardware the services are on, just concentrate on the service itself.
Create a third table for the relationship between services and hardware. This is where you associate a particular service with a particular piece of hardware. You may well want to keep begin and end dates here too, so you have a relationship history.
Repeat this process as necessary. It makes for many more tables, and it probably uses a bit more disk space, but it is enormously easier to understand and maintain!
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.