Home Inventory Database Design for Blue Spruce Inventory

From 2010-2012 I operated a home inventory service that would document all of a client’s belongings so they would have the necessary information if they need to file a theft report or an insurance claim. In order to quickly and accurately document and categorize a client’s belongings,  I created a Microsoft Access database that allowed for quick entry of information. The database created custom, easy-to-read reports for clients and a text file to allow information to be uploaded to a client’s KnowYourStuff.org account.

Purpose: I needed a database that created easy-to-read custom printed reports that documented everything about any item in a client’s home. Information was sortable either by item category or location. Each item had a picture, description and quantity while some items also had serial numbers, model numbers and other important information. The database needed to allow for quick entry of items since each house created a lot of information.

Solution: I initially started creating the database using OpenOffice Base since it was a free, open-source solution. Unfortunately, a bug in the report creation did not allow me to continue using it and as much as I am a fan of open-source solutions I had to recreate the database using Microsoft Access. Each customer had their own database file that was included on a CD with their printed reports should they want to update the information after the initial inventory. I later added a report that generated a CSV file that allowed me to upload the information to a KnowYourStuff.org online profile.

Challenges: One of the biggest challenges was with how people organized their belongings. Only listing items by room made for some very confusing reports, especially when it came to kitchens. When looking at a report that listed all the items in a kitchen (forks, spoons, crockpot, pans, waffle iron, blender, etc.) I realized that we identify storage locations within rooms with our belongings. The small kitchen appliances go in the island cupboard. The coffee cups go in the corner cabinet. The cooking utensils all go in the drawer next to the oven. This prompted me to create a Location table along with the Room table to help organize how items were listed.

The varied information for each item also caused some headaches when creating good looking printable reports since some serial numbers were only 4 characters while other serial numbers were 20+ characters. I also did not realize how time consuming it would be to include images with each item when populating the database.

Outcome: Once I was done adding features, I ended up with a solid database with good looking reports that sorted items by location or by category that contained all item information including images. The only thing I would change would be the Forms to input data. They are not very user friendly since I was the only one using them.

Download a copy of SampleInventory.pdf

Leave a Reply

Your email address will not be published. Required fields are marked *