Initial Database Design Issue...

[GlennOwns]

Soon, I will be starting an access database to inventory an entire building's IT assets which include computers, monitors, laptops, tablets, servers, switches, taclanes, etc...

All of these items have Serial#'s that are.... quasi-unique... We are moving 10,000+ items to a new building soon. We want to do this in an organized fashion. Since we already have a sorta accurate inventory of current assets, we were needing a way to provide new asset locations w/o problems ("where's my 3rd monitor duuddeee???").

We we have data like this:

(PK) ID: Autonumber
Serial#: Text (Unique)
Type: Text
Model#: Text
Vendor: Text
Price: Currency
Owner: Text
Account Number: Text
Date Acquired: DateTime
etc...

None of this was relational due to lack of ability... I made it relational which is not necessarily reflected above..

Anyway, the IT manager recently took an entry-level database class and is calling shots on how the database will be built. Every time I spoke of anything like 1NF or non-key attribute, etc... you know, BASIC database design, he would go into another 4-5 minute spiel about how he would walk around and scan things and it would "update" in the database. And I couldn't get him to see eye to eye.. I tried to say things like "if item A moves, it's primary key would ALSO have to change" and he would come back with things like "ITEM A SHOULDN'T MOVE WITHOUT MY PERMISSION AND IF IT DOES I WILL UPDATE THE INFORMATION IN THE DATABASE".... Anyway, I don't think he'll notice that I will still design it my way (haha) but there's one particular thing I had a question on...

Scenario: The IT Manager wants to make it easier to locate assets in the building. We are going to track an asset's Serial #, Desk #, Room #. There are many rooms in the building so Room# is a given. However, there are many assets in the rooms (some rooms have 50+ computers). So identifying a station/desk for an item will be VERY useful..

The IT Manager came up with his own control number for this.
Room #: Standard building room number like 11061. Each room # is unique.
Desk #: Room # + object type + serial numbering such as:
11061-Desk1
11061-Desk2
11062-Desk3
11062-Desk1
11062-Shelf1
11062-Rack1

So each desk/shelf/partition/communication closet/etc... will have a hand-made barcode like that. I told him that those barcodes aren't classified as unique identifiers. If a desk changes rooms, its barcode must change. If a desk is moved inside a room, its barcode must change (the 1, 2, 3 helps ID where they are in the room). So I would have to say that the above scheme for labeling/barcoding workstations/desks is INCORRECT because the unique key is not permanent to the object...

So I told him we should INSTEAD print labels with prefix+autonumbering like:

Item-00001
Item-00002
Item-00003
Item-00004
Item-00005

These items will be stored in a separate table with additional metadata like:

Room: 11061
Area: North-east corner.
(PK) ID: Item-00002
Description: Blue Workstation desk.

Room: 11061
Area: First desk on left.
(PK) ID: Item-00009
Description: Blue Workstation desk.

Room: 11061
Area: North-west corner, 2 desks down
(PK) ID: Item-00045
Description: Metal rack

Room: 11070
Area: 2 racks to the right
(PK) ID: Item-00144
Description: Server rack for Network1 devices only

Anyway, I thought that creating a table query for locations would aid in identifying the 4000+ pieces of furniture that we'll be "storing" the IT assets on with barcodes...


Please let me know if it would be a good idea (for an IT Asset inventory database) to control non-IT items for the sole purpose of maintaining data integrity of those non-IT items... If you can convince me with some smart verbiage, that would be great as well!


Thanks.

 

[jdraw]

You should research Normalization - some of your concepts/descriptions need some refinement.
Each thing you intend to inventory should have a unique identifier -- that's what identifies that thing from all other things. The thing didn't change because it was moved from Room 5 to Room 200 -- only its current location changed.

Also, concerning your proposed set up, do you really need all the detail? How will it be kept current (so your database will reflect reality)? How does your company keep its IT inventory now?

Here are some materials to help:
Relational Database Principles
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Normalization, ER DIagramming, Better Databases...examples
http://www.rogersaccesslibrary.com/forum/topic238.html

Free data models that may help with diagrams and design
http://www.databaseanswers.org/data_models/index.htm

I posted a sample of moving equipment from one location to another in Post #14 at
http://www.accessforums.net/forms/how-swap-records-separate-tables-using-form-24504.html

Good luck with your project.

 

[spikepl]

I agree with jdraw. On the face of it, you are proposing a lot of both initial AND subsequent maintenance effort, for unclear longterm benefits. I have been around here and there and have never ever seen locations physically tagged alone for the purpose of keeping tabs on stationary items like your IT equipment is likely to be.

 

[ButtonMoon]

GlennOwns,

You can't always rule out the use of some some candidate key just because its values could change. Stability is usually a desirable property for a key but stability is a relative rather than an absolute concept. In reality it's not uncommon for the identifiers of things to change and in many cases it isn't practical to prevent key values changing in a database or to verify whether they have changed in the past. So it isn't right to say that a location-specific barcode isn't a unique identifier just because it may have to change. You just need to be clear about what it identifies and how it will be used in the case where something changes location.

There are surely cases where it would make sense to label physical locations with their own coding scheme, e.g. a warehouse, shop floor or storeroom. Presumably you would still want to record the serial numbers of each piece of equipment as well and model the association of each item with its physical location.

It's also worth considering that arbitrary numerical sequences don't necessarily make good identifiers for human beings to read or record. The design of identifiers and their format can make a significant difference to usability and accuracy in a system. It's worth giving some careful thought to features like the choice of numeric ranges for identifiers, the inclusion of check digits and allocation of any special purpose codes that your users might find helpful.