Asset management dealing with groups of items and indivual items

[Darryn]

Hi all

I am in the middle of developing an asset managing/inventory database
system for outdoor recreation equipment. Based loosely on the asset
tracking template in Access97

I am stuck on how to model the following scenario

Some pieces of equipment in the inventory are group of individual
items. The instructors only want to count the things as a complete
set (an abseiling kit, comprising of some carabiners, gloves and a
piece of hardware)
The manager wants to know how many of each individual item are
required to make up complete sets, as he buys xx number of carabiners
and yy numbers of gloves to make up the complete sets.

Also some of the items are counted individually when they are used for
a different purpose

Any suggestions are appreciated

Darryn
-- remove two eyes to reply!

 

[TC]

Try something like this.

tblItem
ItemID (pk)
Description (harness, carabiner, airtank, wetsuit)

tblKit
KitID (pk)
Description (abseil kit, scuba kit)

tbkKitItem
KitID ( composite
ItemID primary key )

That would handle a one-level breakdown.

To define carabiner as a necessary part of abseil kits:
INSERT INTO tblKitItem (KitID, ItemID) VALUES (888, 999);
where 888 is the KitID for abseil kits, and 999 is the ItemID for
carabiners.

To count the total number of abseil kits:
SELECT COUNT(*) FROM tblKit WHERE KitId = 888;

To count the total number of carabiners (in whatever kits they are used):
SELECT COUNT(*) FROM tblItem WHERE ItermID = 999;

HTH,
TC

 

[TC]

Er ... what drug was I on here?

The structure that I suggested, assumes that tblItem contains a seperate
entry for each physical item - not just for each *type* of item. So if you
had 8 carabiners, there would be 8 rows in tblItems.

Similarly it assumes that tblKits contains a seperate entry for each
physical kit - not just for each type of kit. So if there were 8 abseil
kits, there would be 8 rows in tblKits.

So the item description (carabiner) should *not* go in tblItems, and the kit
description (abseil kit) should not go in tbKits. Those descriptions should
go in sepertate tables:

tblItemType
ItemTypeID (pk)
Description (harness, carabiner etc.)

tblKitType
KitTypeID (pk)
Description (abseil kit, scuba kit)

then the relevent *ItemTypeID* should go in tblItems (as a pointer to the
relevent Item Type record), and KitTypeID should go in tblKits.

tblItem still makes sense: it stores the details of a specific item (not
just type) of equipment - eg. serial number, date last checked, batch #,
purchased from, etc. tblKits stores similar details pertaining to kits.

Sorry for any confusion!

HTH,
TC

 

[Darryn]

Er ... what drug was I on here?

The structure that I suggested, assumes that tblItem contains a seperate
entry for each physical item - not just for each *type* of item. So if you
had 8 carabiners, there would be 8 rows in tblItems.

Similarly it assumes that tblKits contains a seperate entry for each
physical kit - not just for each type of kit. So if there were 8 abseil
kits, there would be 8 rows in tblKits.

That is something else I have been agonising over!

The major items in the inventory are numbered ie vehicles, wetsuits,
wetsuits, backpacks, tents. Others are just counted, the abseil kits,
snorkelling kits, barrels etc etc.

Obviously an item which has been individally numbered should have a
separate record, but I am not sure if I need this for the counted
items. I know with a separate record I could keep better track of
the individual components (some have a finite lifespan as a matter of
safety policy)

So the item description (carabiner) should *not* go in tblItems, and the kit
description (abseil kit) should not go in tbKits. Those descriptions should
go in sepertate tables:

tblItemType
ItemTypeID (pk)
Description (harness, carabiner etc.)

tblKitType
KitTypeID (pk)
Description (abseil kit, scuba kit)

then the relevent *ItemTypeID* should go in tblItems (as a pointer to the
relevent Item Type record), and KitTypeID should go in tblKits.

tblItem still makes sense: it stores the details of a specific item (not
just type) of equipment - eg. serial number, date last checked, batch #,
purchased from, etc. tblKits stores similar details pertaining to kits.

It seems like I need to modify my table design quite a lot, so far my
my main equipment table looks like this with a lot of small lookup
tables attached to it.

Record ID (pk autonumber)
CategoryID
SubCategoryID
Equipment Description
EquipIDNumber (have not used this as PK as it is not unique and
does not apply to all items)
Equipment Description
ManufacturerID
Model
SerialNumber-idcolourcode
SizeID
ColourID
Finitelifespan T/F
Lifespan
StatusID
Acqusitiondate
Disposaldate
PurchPrice
SupplierID
Comments
EquipmentQuantity

I had done this as it seemd to be the easiest way for me to design the
input form as I will not be on hand to support the system once it has
finished.

I think I need to have BIG rethink and break it down into smaller
tables for all of my sub categories as well as the kit/individual
items

Sorry for any confusion!

No you have been a great help so far
Thank You

Darryn
-- remove two eyes to reply!

 

[TC]

I had done this as it seemd to be the easiest way for me to design the

input form as I will not be on hand to support the system once it has
finished.

Darryn, this is a common but mistaken approach. You should design the tables
based on the nature of the data that you need to store, and how it all
relates together. You do not consider form or report design *at all*, at
that stage. Form/report design comes later!

I think I need to have BIG rethink and break it down into smaller
tables for all of my sub categories as well as the kit/individual
items

I think that would be time well spent. See if the following article helps (I
can't remember whether I suggested it to you before, or not):

http://support.microsoft.com/support/kb/articles/Q100139.ASP

Good luck!
TC