Equipment Inventory Solution

[wjonsmith]

Hi all,

 

I'm trying to create a database solution to keep track of all of our equipment at work. This includes things such as computers, computer accessories, printers, camera equipment, etc... Each item would include some details about the product such as manufacturer, model, specs. There would also be a field for the user that the product is currently assigned to. 

 

I'm fairly new to database theory and wanted to get some thoughts on how to get started. Here is a rough breakdown of the tables I think I would need:

 

=======

 

product

• manufacturer
• model
• category (computer, camera lens, etc...)
• specs

 

item

• product
• serial
• barcode
• user assigned

 

user

• name
• location (office)
• items currently assigned

 

 

=======

 

One of the issue I envision is that different products will have different spec fields (i.e. computers: RAM, processor speed – camera lenses: focal length – monitors: screen size). Is it better to have a table per item category? Is there a way to keep it all under one table but have just the relevant fields be displayed when entering / viewing data? Any guidance is much appreciated.

 

Thanks in advance!

[Matthew F]

I think this would depend on how important the specs data are. If the user is only going to be doing some casual browsing, then you could just leave it as a text field. If you want to do more with it (e.g. sort and filter records) then you may want to define specific spec fields. Create different layouts to display the specs relevant to a particular item type.

[Matthew F]

You might also be contemplating a child table of specs themselves. This would provide more flexibility in terms of the number and type of specs per item. Yet it might be less convenient for the user; e.g. if spec fields are generic then they will need specify the type of spec.

I would try to envision look and function of the final product, and think about how users would interact with it. How much time should be devoted to data entry vs. searching, browsing or printing? Then design the database to match. In the end, the best database design is one that provides exactly what the users need, quickly and easily.

[wjonsmith]

Thanks Matthew, that is a great point. After thinking about about it I think I’ll need a little bit of both. 

 

I think a general specs/notes text field will be perfect for most of the products and make it easier for data entry. On the other hand I want to make sure if someone is inputing a new product that certain specs always get entered, especially the computers. Often I’ll need to bring up specific information on a machine such as what operating system it’s running.

 

When you said “Create different layouts to display the specs relevant to a particular item type”, how would that work? Is that one table with fields for every spec and the layout will change displaying only the relevant specs once you choose the product type? How would I get that to work within FMP?

 

Could you also go into more detail about child tables and how that works? 

 

Thanks again. I’m trying to wrap my head around this whole thing.

[Matthew F]

 

 

Is that one table with fields for every spec and the layout will change displaying only the relevant specs once you choose the product type? How would I get that to work within FMP?

 

Exactly.  The user would first set the item type in a designated field.  When this field is saved it would fire a script trigger that checks the field and changes to the appropriate layout.  A "computer" layout would prompt the user for RAM, OS, drive specs, assuming that you want to track those things.  

 

 

 

Could you also go into more detail about child tables and how that works? 

 

Using a related (child) table would be an alternative approach.  Instead of the specs going directly on the Item record, you would view a related "Specifications" table on the Item record via a portal.  The two tables would be linked via Item ID number (Since they're linked by Item ID the Item table is the "parent" table).  You could create and delete related specs directly in the item record via the portal.  I wouldn't necessarily take this approach, unless you really want to force users to catalog specs in detail. To be able to handle specs of different sorts this table would need very generic field names and the users would therefore be forced to do more characterization. (e.g. with fields called "spec type", "quantity", "Unit", "characteristics")

 

Using a related table, and a portal, might be more appropriate for related repetitive data such as inventory or maintenance checks for each item.