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