Database for managing computer assets - how to relate tables

[HeislerKurt]

I’m building a database to manage computer assets in an organization.
I’ve found several examples (including the MS Access template) but
none which take into account more complicated data needs.

The data model I’ve created so far is pictured here:
http://img155.imageshack.us/my.php?image=datamodelforassetdatabase3.jpg

The entities are:

- Buildings
- Rooms
- Wall Jacks
- Employees
- Assets
- AssetType (e.g., PC, monitor, printer)
- AssetSubType (e.g., LCD, CRT)
- AssetManufacturers

BUILDINGS, ROOMS, AND EMPLOYEES

1. A building can have one or more rooms.
2. A room can have one or more employees.
3. An employee can be assigned to one or more rooms (most likely in
different buildings).
4. A room can have one or more wall jacks.

ASSETS

For each asset, I need to store the following details:
- Which employee it’s assigned to (but some assets - like a network
printer in the hallway - won't be assigned to a particular employee)
- Which wall jack it’s plugged in to
- Its asset type (e.g., computer, monitor)
- Its asset subtype, if any (e.g., LCD, CRT)
- Its manufacturer
- Its serial number, etc.

1. An employee can have one or more assets (e.g., a computer, a
monitor, and a printer).
2. An asset can be plugged into only one wall jack.
3. A manufacturer (e.g., Dell) can be assigned to one or more assets.
4. An asset type (e.g., computer, monitor) can be assigned to one or
more assets.
5. An asset type can have one or more subtypes (e.g., a monitor can be
either an LCD or CRT; a computer can be either a "small form factor"
or a "midsize tower").

###

I can’t figure out these issues:

1. How to relate tblWallJacks to tblAssets. If I make a link from the
PK of tblWallJacks (WallJack ID) to a FK of tblAssets (WallJackID),
Access will see it as a one to many. This would be wrong because a
wall jack can have only one asset, and vice versa. Perhaps I shouldn’t
make a link, and instead just use a lookup table for WallJackID in
tblAssets. On a form, I’d use code to synchronize the WallJack combo
box options with the Room selected in the Room combo box.

2. Since an employee can be assigned to more than room, which may be
in a different building, do I need to make a relationship between
tblEmployees and tblBuildings, or will the tblRoomToEmployees junction
table establish the relationship?

3. How to handle the situation when an asset isn’t assigned to a
particular user (like a network printer in the hallway). Perhaps I
could creating a fake user? The room could be called “Hallway1” with
wall jack “X1” for example. I would then assign the asset to the fake
user, who lives in Hallway1. I’d have to create additional fake users
to handle other “hallway” assets, which could pose problems

Thank you for any advice on this!

Kurt

 

[KARL DEWEY]

I’m building a database to manage computer assets in an organization.
Will it be to track property or configuration management or both?

Location - LocID, Building, Room, Spot
Employee - EmpID, LName, FName, etc.
Asset - AssetID, Serial, Model, Type (e.g., PC, monitor, printer), SubType
(e.g., LCD, CRT), Manufacturer
System - SysID, LocID
SysAsset - SysID, AssetID, EmpID

1. How to relate tblWallJacks to tblAssets.
You do not. Wall jacks will be like software applications on the computer.

2. Since an employee can be assigned to more than room,
The asset is assigned to a location and an employee assigned assets as
property owner - not rooms.

3. How to handle the situation when an asset isn’t assigned to a
particular user (like a network printer in the hallway).
It needs to be assigned to someone for tracking purposes.

 

[Fred]

Coming at it from a slightly different angle than Karl, I think that you are
asking the tail (Access structure) to wag the dog (defining the
"recording/storage" aspect of your mission. The first thing is to
explicitly define the "recording/storage" aspect of your mission. This
includes:

1, Items
2. Real world relationships. (e.g. locations of equipment in rooms,
connection of computers to jacks, assignment of equipment to employees etc.)
These are changing relationships that you seek to record/track rather than
permanent attributes of an item. For example, the name of the manufactuere of
asset #12345 is probably a permanent attribute.

Although there is often overlap, the list of real world relationships that
you want to record will be different than Access relationships. Where the
relationship is permanent, it is recorded by placement in the same table,
even if an ID# / lookup table is used for normalizaiton pruposes.

I suspect that your "items" list will probably be:

- Buildings
- Rooms
- Wall Jacks
- Employees
- Assets

And that the following are merely attributes, albiet that they still may be
tables for lookup or normalization purposes:

- AssetType (e.g., PC, monitor, printer)
- AssetSubType (e.g., LCD, CRT)
- AssetManufacturers

I suspect that once you do the above, you sound like someone who would then
be able to figure out the structure, but if not, would suggest reposting with
the answers to the above.