Results 1 to 10 of 10
  1. #1
    DatabaseManiac is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    2

    Construction Heavy Equipment Database Design

    I hope this is the right place to post this question. I work as a manager for a construction company and I am attempting to design a database that can track our inventory of heavy equipment (approx 40 pieces) and the specific filters that each machine takes. For example, I would like to be able to run a report for all equipment that lists significant info about the equipment (which is in a table named Equipment) as well as pulls information about the types of oil, air, and hydraulic filters that those pieces of equipment take. I have created a separate table for each of these inventory of filters named (Oil filter, Air filter, hydraulic filter) each with a unique filter ID which I've used as the primary key.



    I separated all of these tables because my feeling is that I need a bridge table to be able to achieve the database design I am looking for.

    I have attached a jpeg of my current relationships window
    Attached Thumbnails Attached Thumbnails AccessPicture.PNG  

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You may only need one table. Two maximum.
    All 4filter tables have the same structure and should be one table. Is the filter type field going to contain Air, Oil etc? If not, you need an extra field for that.
    If this table is separate from the main table, then remove FilterType from that table. FilterID is all you need.

    BUT do you really need two tables? Presumably there will only be one record for each FilterId so you could just add FilterMenu to the main table
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yeah, you probably dont want extra tables , (1 for each filter)
    instead youd have a tService table and put what filter in that

    EquipID, Item, SvcDate, make, model,
    123, oil Filter, 1/1/18, xxx, xxx,
    123, Fuel Filter, 2/1/18, xxx, xxx,

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If you record mileage, you should include an AsOfDate.
    Does the servicing of these vehicles always occur in your location and by your service personnel?
    Could a vehicle be serviced by some other garage/service depot?

    Here are a couple of free data models: you can cut/add as needed
    http://www.databaseanswers.org/data_...ance/index.htm
    http://www.databaseanswers.org/data_...ance/index.htm

  5. #5
    DatabaseManiac is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    2
    I was thinking the exact same thing but wouldn't that mean that I would have alot of repeating data. For example, if equipment piece #1 had a filter that went with it that had a filter ID of A1 which would be classified as an oil filter... If I wanted to also attach an air filter to that same piece of equipment I would have to re add all of the identical information except under filter ID and type which would create a lot of different but almost identical entries.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might start out with a structure like this:
    Click image for larger version. 

Name:	Construction1.png 
Views:	20 
Size:	40.0 KB 
ID:	32786

    Use a main form/sub form to enter maintenance data or find the correct filter.
    You can enter more than 1 type of filter for each piece of equipment:
    3 air filters - 3 different manuf
    4 Hyd filters - 4 different manuf
    1 fuel filter
    2 oil filters - 2 different manuf
    Attached Files Attached Files

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can use existing record, to 'copy' from rather than reEnter all that data again.
    combo boxes

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How many tables? How complex is the business (e.g. are there divisions within the company) and how much control do you want to exert to promote accuracy?
    Let's say you purchase a new piece of equipment and are going to set it up and you allow free form entry for equipment type. Later, you create a query wherein the query uses the type field, but you don't get the new piece in the results. Why? Because the free form entry became loadre when it should have been loader. You avoid this by having an equipment type table and force the db user to pick from a list. That's just one example of a possible extra table.

    I was an equipment reliability specialist and diesel mechanic for a time and am probably rusty on the theory of db design for that, but can vouch for how complex the design could be. Here it seems the scope of this db is much simpler. On that note, to perform a service, you would need to join each filter table in a query to get a list that includes each type, but you would need the equipment id in each table to be able to associate a filter type with the equipment. Not only that, you need to join every table each time, so why split them? So you usually cannot escape duplicating data, but you do it in a way that supports normalization. Any time you need a new table or field in a table to support a new type of an entity (not talking about an attribute that was overlooked) you have usually made a design error.

    Then there is the probability that you will need substitute filters due to pricing or availability, so you want to be able to pick the same filter type for the same machine, but be able to buy Cat one time and Donaldson the next time. Or maybe you want to allow for different suppliers for the same item in some cases (like batteries). Taking all the above into consideration, you should be able to see that data duplication is pretty much unavoidable, but it needs to be done properly and with sound reasoning.
    Last edited by Micron; 02-28-2018 at 03:01 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron. Once you determine how much of the business will be supported by this database, you can build a logical (high level) model to identify the "things" involved. Even if your priority is Equipment service--filters at the moment, you can see where this fits within your larger model. Some planning now will help fit thing together later.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Forgot a point: when designing, consider where/how you're getting engine hours from. You might have rentals and pay for gps monitoring service, so will they give you that data? If gps isn't involved or you have to manually collect the hours data, you might want to incorporate time based service, in which case you'll need 3 time fields, not 1: (last service hours, current hours, and service interval). Consider something as robust for date based service intervals as well (last service date and interval, but not next service date - that is a calculation). You also want to know how you'll handle replaced hour meters that start out at zero.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. construction of the Database
    By Erictsang in forum Access
    Replies: 4
    Last Post: 11-04-2017, 11:29 PM
  2. Replies: 24
    Last Post: 09-09-2017, 09:38 AM
  3. Heavy equipment table hierarchy
    By Stephenson in forum Database Design
    Replies: 21
    Last Post: 11-08-2016, 07:05 PM
  4. Equipment Database Design
    By cap.zadi in forum Database Design
    Replies: 3
    Last Post: 02-26-2013, 02:20 AM
  5. design equipment management
    By chanlongs in forum Database Design
    Replies: 0
    Last Post: 07-14-2009, 06:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums