How to determine Database Schema in Access

Posted on 2014-11-14
Last Modified: 2014-11-17
I am building an Asset tracking and management database.  The assets are assigned to one of several offices.  I have a one to many relationship set up between the assets table and the office table (one office can manage many assets).  My question is in regard to the assets table and the type of asset.  For example I need to track expenses for vehicles, but also buildings.  Each asset type will have significantly different items to track for example :

Vin, Year, Make, Model, Color, Engine Size, etc.  


Address, CIty, State, Appraised Value, Assessed Value, Water Provider, Power Provider, etc.

There are other asset types and items to track for each but this is an example of two.  I'm not sure how to set up the tables to be able to track all the different things I need.  Would I make several one to one relationships with the main asset table based on asset type?
Question by:yoducati
  • 6
  • 5
  • 2
LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 40443132
First, I would twist that around: The company has assets, which are located in one place or assigned to one person.

Second, you're going to want to look at what's called a EAV (Entity, Attribute, Value) design.   But I would suggest a hybrid approach.

 Have one table for Assets and include everything that would be in common, such as Placed In Service, Cost, Assigned to, Expected life, etc.

Then use an EAV approach to record all the details of each that you want to track.  Here's some links to some old threads that discusses all this:

This one has a good overall attribute design:

This one mixes a asset tracking database with a technical one:

This thread talks about some of the pros and cons with this type of design:

The first is along the lines you need. Read through the last thread to see the issues with this type of design and also this one:

Which is a thread where a bunch of the Expert's here thrashed out the pro's and con's of this design type.

After digesting all that, I think you'll need to decide on either:

1. A strict approach - one table for each type
2. The hybrid approach - one central table for the shared attributes and then EAV tables to describe the non-shared attributes.

As I suggested at the start, I would use #2.  

Please bounce back with any questions you might have.

LVL 34

Assisted Solution

PatHartman earned 100 total points
ID: 40443264
I have a successful, working example of the EAV model.  I built it because the client's business was dynamic.  The company sold specialty line insurance policies and the types of policies dictated the attributes that were required.  Their existing system used the more traditional 1-1 relationships to store data for different product types.  The problem with the solution was it was taking their IT department up to 6 months to add a new product and all the supporting tables/forms/reports/queries and that was interfering with business.  The app I built ended up with a table that defined attributes including their data type and another table that assigned attributes to a specific product and indicated whether a value was required or optional.  When a user started a new policy for a particular product, the app ran an append query to append a row for each attribute.  We ended up with close to a million rows in the policy details table before Access started to bog down and we converted to SQL server.  The client archives the expired policies after a year to keep the row count down.  Otherwise, they'd end up adding over a half million rows per year.

As others have mentioned in the links, what the EVA gives you in flexibility, it looses in efficiency so you wouldn't take that course lightly.  If the attributes are known, stick with the model suggested by Jim where you store all the common attributes in one table and then create 1-1 tables or EVA for the odd-ball attributes.  

The problem you run into with the flat table model is that, at least with Access, you could easily run afoul of the 255 field limit to a query if you have a lot of 1-side tables to join in so you'll have to carefully plan your data entry forms and reports to work with one asset type at a time.

Author Comment

ID: 40443327
Hi guys.  Thanks for the responses.  I'm a little confused about how the EAV structure looks and functions.  As far as efficiency is concerned the long term plan is to have this running on a dedicated SQL server but for now Im just trying to get an idea of what the structure would look like and how it would function.  Im very comfortable with access and Im just using it to get an overall picture in my head of the structure.  I had already started with a hybrid approach (albeit accidentally) as it just made sense to me that the main assets table would have the standard data for each asset within it.  Without any knowledge of this EAV structure I sort of defaulted to the 1-1 setup but thought their must be a "better" way.  Having looked at some of the examples could you guys maybe help translate that setup into what it would look like for me?  I think I understand it in theory but I'm not quite sure I understand how to put it into practice for my situation.
LVL 57
ID: 40443360
Take a look at this comment from the first thread I posted:

At a minimum, you need three tables:

1. tblAssets - this is the table with all the common fields for any asset.

2. tblAttributes - This is the a table that lists the attributes you can pick

3. tblAssetAttributes - This is the table that contains the value for each Asset/Attribute Pair.

  Now you may want to add a little more, say grouping the attributes by class so that the attribute "VIN" is used along with "make", "model", etc   and when I have an asset of a "car", I have shortened list of attributes that I can choose, which only apply to a car (this is what you do when you design a relation, which is a table).

You may want to take it even further by saying for each attribute, it must be of a certain type (date, text, etc), be in a certain range, etc.

That what your were looking for or no?

LVL 57
ID: 40443369
So from a practical standpoint for a form, you'll have a main/sub form combination.

Main form will be bound to tblAssets.

Subform tblAssetAttributes, with the attributeID and the value for that attribute.

You'll use a combo control for the attributeID, which will let you choose from tblAttributes.

If you group attributes, then you'd select that in the main form when defining the asset, and in the subform filter the combo control with that so you get a shortened list.


Author Comment

ID: 40443376
The description sounds exactly like what I'm trying to do.  Especially that "adding a little more" part.  My problem is I am definitely a visual learner so seeing this all in text is hard for me to picture.  I was already looking at that post and decided to recreate those tables, link them up, and see what it looks like.  I think I will have a much better understanding of how this works once I do that.  Im working on it now so let me see what I can do and I think this will all make much more sense but the descriptions are spot on.
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 34

Expert Comment

ID: 40443379
The data would look like:
RowID	ParentID 	AttributeName	AttributeValue
1	        378	                 Address	               101 Main St.
2	        378	                City	                        Nowhere
3	       378	                State	                NY
4	       378	                AppraisedValue	450
5	       378	               AssessedValue	375
6	        378	               WaterProvider	Clean Water
7	       378	               PowerProvider	CL&P
8	      764	              Address	               10421 NW 17th Ave.
9	      764	              City	                       Miami
10	      764	              State	                       FL
11	      764	              AppraisedValue	189
12	     764	              AssessedValue	189
13	    764	               WaterProvider	Water Lilies
14	     764	             PowerProvider	FP&L

Open in new window

Sorry, I can't get it to line up
LVL 57
ID: 40443431
<<My problem is I am definitely a visual learner so seeing this all in text is hard for me to picture.  >>

 Walk through that first thread.... it contained an example and look at what Pat posted as well.

 Actually doing it is a great idea...setup a small sample database, walk through adding a "car", "printer", and a "desk".

 You'll find the three have some arbitrates in common, like height or manufacturer, but only a car will have a VIN, where as a printer will have a serial number, and a desk only a asset tag.

LVL 57
ID: 40443440
I should add that this design works well when you want to only  reference or search the attributes.  But if you need to "assemble" the complete asset record often (the common fields plus all the attributes for a given asset), then it's not so hot as Pat pointed out.

 That's why the hybrid design works well.  You can easily query on the main asset table, then search/reference the attributes as needed.


Author Comment

ID: 40443444
Ok  working on it now.  You guys are too fast which is awesome!  So I just want to make sure I understand the three tables.

1. tblAssets - this is the table with all the common fields for any asset.

I would consider this my main table.

2. tblAttributes - This is the a table that lists the attributes you can pick

This is essentially a lookup table that shows the values I have entered in the table to the user.

3. tblAssetAttributes - This is the table that contains the value for each Asset/Attribute Pair.

This is a junction table with a multiple field primary key that links the two others together making each record in it unique and is also where the user entered data is stored?

Author Comment

ID: 40443464
I will need to assemble the asset record regularly so I think the hybrid version is definitley the one for my setup.
LVL 57
ID: 40443466
You got it...


Author Comment

ID: 40443623
OK thanks for the help guys.  This is awesome.  I haven't done the forms etc yet but I've got the table structure and relationships set up and it makes much more sense and seems very clean to me.  I'm sure I will have more questions as I work my way through all of this but this definitely takes care of the initial structure problem.

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Read about achieving the basic levels of HRIS security in the workplace.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now