Results 1 to 4 of 4
  1. #1
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    Forklift Maintenance Concept Example db

    I've started a new thread for what seems to have turned out to be more involved/invasive than I had ever planned, and this is more around a db modeling project as opposed to the original issue which has reached 3 pages: Calculate the difference between two rows in a query

    Sample db's were posted there, but my take on the db diverged significantly, based on certain presumptions around business needs. It is slanted towards the needs of a maintenance & rental provider as opposed to what might be required where the equipment maintainer is also the owner. As you read, keep in mind that it fits a business relationship model I have in my head, but have some experience with, although not from the point of designing a db for. Thus it may be flawed, assuming you can wrap your head around the business case and do spot a problem.

    Thanks to ssanfu for providing the initial db as a start!

    Some notes on where this one differs in case you have or will download any examples from the other thread:
    - some fields were indexed and field size limits reduced where it seemed appropriate
    - some field types altered (e.g. Capacity, number>text; allows 1000 lbs or 1000 kg)
    - changed Forklift to Unit throughout; allows lifting equipment that isn't a forklift (e.g. pallet lifter, reach type units)
    - ServiceTbl:
    - removed NextServiceHours (should be calculated by form)
    - chgd ForkliftID_PK to ServiceID_PK as entity is service, not unit)
    - removed Sequence (thread solution should negate need)
    - TechniciansTbl:
    - removed names & initials fields (data that likely exists in employee table; possibly table not req'd)

    - added ServiceTypeTbl & ServiceType_FK to what was ForkliftServiceTbl

    - based service on Unit rather than unit/customer junction. Retrieving maint. history would require customer criteria in a query?
    Notion is that maintenance data/history belongs to the unit.

    - for each unit under 1 contract, contract info (e.g. PO, ContractNo) would have to be repeated

    - pricing setup depends: based on contract or unit? Modified so that pricing doesn't have to be under contract. Plus, I could not add pricing data for a unit unless there was a contract, meaning no temporary rentals were allowed without contracts. Thus I associated contract with unit ID.

    - comp index on ContractNo and PO_NO to allow dupes, but not as a pair.
    - comp index on customer name/address/street for cases where same customer name would exist because they have different plants/locations

    - added ContactsTbl joined on jnctUnitContact (also added): allows multiple contacts per unit per area, rather than 1 contact only per customer

    - removed Area field from CustomerTbl in favour of adding a contacts table so as to relate 1 or more contacts to a unit

    - added jnctUnitContracts (linked to UnitTbl and ContractsTbl): permits having a contract for unit or not, as previously mentioned



    Here is pic of relationships. Would appreciate feedback before I post the actual db, for which I'd like to hold off until I can enter records and test my design theory. Don't be too hard on me!

    Anything else I've forgotten by now and it's late, so everything's starting to mesh. Might be evident in the design...


    Click image for larger version. 

Name:	FLdb1.jpg 
Views:	24 
Size:	75.6 KB 
ID:	36535
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Micron,

    Do you have a list of "Business facts" that you (and/or the OP and/or ssanfu) have assembled?
    Some test scenarios and test data would be required to "test/vet" the model as you have identified.
    Perhaps PeterS could lead us through a clear "day at the office" description in order to help the vetting and any clarification of facts. I agree that the model (tables and relationships) have to support the business, and have to be "accepted" before too much physical database is in place.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Do you have a list of "Business facts" that you (and/or the OP and/or ssanfu) have assembled?
    Not from OP at this point. Any db posted in the other thread was only remotely related to the original problem, which I think I was able to solve by providing a subquery as a main query field. You got me thinking though.

    Let's say the scenario is that the db is primarily a tool for a service provider, not the equipment owner and
    - a customer can have 1 unit at 1 location, rented or leased (rl) from the service provider (sp) and maintained by sp
    - or the unit may be owned by customer, who is only contracting out its maintenance and repair
    - the rl or maintenance may or may not be under contract but likely would be at least under a PO
    - the unit may always be at the same location under the same general address
    - there may be 1 or more contacts for that customer/unit
    - customer owned units might be temporarily replaced by rentals if repairs necessitate going to sp site
    OR
    - a customer may have many units spread over many of their locations, either at the same plant or not
    - all other bulleted points above apply, plus likely that there would be multiple units, locations and contacts

    db user should be able to
    - return all maintenance history for a unit, regardless of who currently has it
    - maintenance history should be relatable to the customer. If a rl situation, I think the customer at that point should be retrievable as well
    - modify unit details or add/archive units
    - retrieve contact details regarding a particular unit and know its assigned location

    Not considering a full fledged application at this point, so going beyond technician ID, PO ID, contract ID (be that autonumber fk or actual values) is probably superfluous since there's no consideration for timesheets, invoicing and many pertinent PO and contract fields. I see that data as links to other systems and would remove them from the tables shown.

    That's all I can think of at the moment.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are a couple of models at Barry Williams' site that may be useful.
    Here is one on Vehicle Maintenance and one on Vehicle Rental
    that together may include the Parts and PO stuff you mentioned.
    I guess the bigger issue is "what is the scope of such a project"?

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

Similar Threads

  1. design concept
    By Jen0dorf in forum Access
    Replies: 1
    Last Post: 01-31-2016, 04:44 PM
  2. The concept of updating a database
    By hect1c in forum Import/Export Data
    Replies: 3
    Last Post: 11-16-2014, 08:45 PM
  3. How to setup this concept in tables
    By accessmatt in forum Database Design
    Replies: 5
    Last Post: 09-08-2011, 07:28 AM
  4. Report Concept
    By BLD21 in forum Reports
    Replies: 1
    Last Post: 07-03-2011, 09:44 AM
  5. Concept Forms
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-31-2010, 01:51 AM

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