Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

How to determine Database Schema in Access

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.  

vs,

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?
0
yoducati
Asked:
yoducati
  • 6
  • 5
  • 2
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21963870.html

This one mixes a asset tracking database with a technical one:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Architecture-Design/Q_23398247.html

This thread talks about some of the pros and cons with this type of design:
http://www.experts-exchange.com/Microsoft/Applications/dBase/Q_22474972.html

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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23714122.html

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.

Jim.
0
 
PatHartmanCommented:
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.
0
 
yoducatiAuthor Commented:
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.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Take a look at this comment from the first thread I posted:

http://www.experts-exchange.com/Database/MS_Access/Q_21963870.html#a17372315

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?

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.

Jim.
0
 
yoducatiAuthor Commented:
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.
0
 
PatHartmanCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.

Jim.
0
 
yoducatiAuthor Commented:
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?
0
 
yoducatiAuthor Commented:
I will need to assemble the asset record regularly so I think the hybrid version is definitley the one for my setup.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You got it...

Jim.
0
 
yoducatiAuthor Commented:
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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now