Database Answers Inventory of a Kitchen (Click for Treehugger Web Site)
Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map
Data Model for an Inventory  of Furniture 
Back to the Data Model
A. The requirement is to design a Database to maintain an Inventory of Furniture.

Subject: Database Design
Question: Hello, I have begun setting up a database in Access 2007 for a Rental company 
that tracks assets like furniture and appliances within each apartment unit. 
We have about 14 properties and on average, 40 rentable units per property. 
Each unit contains furniture and appliances that the company owns, but has never kept 
track of what exactly is in each unit and the details of each asset. 

I have already collected a large portion of the data I want to include, but I am struggling 
to figure out how to design the database with so many unique pieces of information. 

I've looked over many of the data models on the website, and they've been very helpful in 
understanding the problem, but it's been difficult to find one that accounts for varied, 
repeated and varied quantities of assets in a collection of locations (with their own 
uniquenesses) that are also defined within different properties. 

Here are the details: 
I have about 20 distinct asset types (eg sofa, television, end table) that contain a 
dozen different asset details which are uniform to their specific models (eg make, 
model, color, size, vendor) 
and also 
unique information: 
  year manufactured and serial number, which are essentially unique to the assets per apartment. 

Each apartment ranges in number of bedrooms, floor coverings, and of course, the quantities of 
assets and asset types. 

I'd like to keep track of aggregate quantities like the number of assets of a specific model of 
television, and also the breakdowns of different assets within each property, but also as a whole, 
i.e. every property. 

I've already separated asset types as different tables (subjects), in which I will use an asset ID 
to identify specific models, 
but here's where I get stuck: 
  how can I begin to divide properties up when I'd like to keep track of unique assets and the aggregate 
information like quantities across properties and also within. 

I'd like to divide the information as much as possible for efficiency and potential for growth/ alterations, 
but how can I do that without having hundreds of relationships, and also property tables that list apartment 
units and 60 or 70 columns describing what Asset IDs are contained within the apartment. 

Any help would be greatly appreciated- I'm sort of new to databases and it's been difficult to visualize how 
to put together this information in a way that is logical and will be functional on a day-to-day basis. 

Largely, the database will be used to track assets, inform purchasing, renting, and capital improvement decisions 
in reference to budget structuring. 

A. The Things of Interest are :-
   A.1 Assets
   A.2 Others, to be determined

B. How are they related ?
   B.1 To be determined

Barry Williams
January 20th. 2011
Principal Consultant
Database Answers


Home Ask a Question Best Practice Careers Contact Us Data Models Search Site Map