Upgrading my CMDB

[markiequarkie]

Goodday to all ,
For my work we have a CMDB , This is a database were all the computer hardware is registrerd
you have to think about for example laptops monitors mobile phones etc etc

On this moment we have 1 table with all the information in it , with a form it is make "visable"
Problem with this is if a user leaves or we have to change the monitor i have to make this changes by hand.

So i want to make some changes to make it CMDB 2.0

my tough was to make a new table for every hardware type and let all the data come together in the form from the user

do you guys agree or would you do it another way?

 

[June7]

How else would you expect to make changes to data if not "by hand"?

Exactly how would having a table for each hardware type "let all the data come together"?

Have you seen Use the Access Asset Tracking template - Microsoft Support

 

[theDBguy]

Hi. Welcome to AWF!

Were you thinking of using a barcode scanner to avoid entering data by hand?

 

[NauticalGent]

my tough was to make a new table for every hardware type and let all the data come together in the form from the user

Bad idea...

 

[The_Doc_Man]

There are arguments for going either way. Unfortunately, all of the arguments for separate tables are bad ones related to trying to cram too much into the table.

We see this kind of problem hashed out dozens of times in a given year. The problem is usage - as in "How will this be used?" To do this, you have to look at the REAL problem. It doesn't MATTER what the object is; what matters is that someone checked it out when they started on the job (or later transferred to the place that needs that stuff) and now is checking it in because they are leaving the place that uses it. Yes, those items are disparate entities - monitors, keyboards, towers, laptops, docking stations, external terabyte disks, etc. Impossible to build a single table to describe everything. But here is the REAL question: How much description is needed?

You have a table of property to be checked in or checked out. Typically in a place that does this, some sort of property tag (with a unique serial number) will be affixed to the property. So you have a table:

tblProps: TagID (the property tag serial number and the table's primary key); property type (Monitor, Tower, Desktop, Keyboard, External Disk, Docking Station); Make/Manufacturer: SONY, DELL, IBM, etc.; Model: Maker's assigned name for that particular model of device; MakerID: The serial number assigned by the maker; Notes: list of blemishes at time of original check-out

tblAssigns: {possibly an autonumber ID as prime key}; TagFK (the tag number from tblProps and a foreign key); PersFK (an employee's internal ID and a foreign key); DateOut (date checked out); WhoAssigned (ID of the person who did the check-out of the person); Location (where the item is, such as a desk ID, room ID, etc)

Then when the item is checked in, you assign it to the keeper of business distributable property.

What else do you need? If you were trying to do something fancy with extra data about the object, you might be overthinking it a little. Oh, sure, you can have a specifications field in each property if you had to, like CPU: 3.6 GHz, 16 GB RAM, 1 TB HDD, ... or Screen 1920 x1080, LED, or USB i/f 2.5 Mbit transfer rate... put that in a text secondary description field. The thing about this is, what would you do (or what would you NEED) with more information than that?

So... you want to see all of the displays? Do a SELECT .... FROM tblProps WHERE PropType = "Monitor" .... ORDER BY Maker, Model ...
and there you go, a list of monitors. Why put that into a separate monitor table?

 

[Pat Hartman]

I agree with Doc, you need two tables, one to define the property and the second to define individuals. All types of property exist in one table. Several attributes will help you to distinguish their type and possibly different processing requirements. There are two relationships for property though. Some of the property is components and these are usually assigned to a major piece of property. So you have printers and monitors and external drives, etc that are associated with CPUs and usually dealt with as a unit and then the CPU is either in inventory, assigned to a department or assigned to an individual.

So, beyond the two basic tables, you need to define how you want to handle the relationships? Will the CPU and each component be assigned to an individual or will the components be assigned to the CPU and only that is assigned to an individual? You can use an attribute to hold Asset type so you know whether the asset is a CPU or headphones. Then the Asset type has a "level" attribute which will tell you that CPU's are primary and primary assets are assigned to individuals but printers are components and assigned to CPUs.

Given that you are using one table, you are having to change every item assigned to an individual when that individual leaves the company. Using the two tables and foreign keys, you can automate that process. So, if an employee resigns, you can make one update to reassign all his assets to his replacement or you can put them back in inventory where they can be assigned later.

You also need to consider whether or not you need to maintain history so that you know for the entire life of an asset, to whom it was assigned and when.

So, there are lots of possibilities for business rules which need to be defined based on how your company wants to manage assets. We can help with what we think are more standard business rules.