Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14

    Help with Database Design / Relationships

    Hello! I'm new to Access and have been watching tutorials on how to use it, but I'm still having some trouble warping my head around designing the database.

    I'm part owner of a permitting service that issues permits to truck drivers that are oversize and/or overweight that need permits to travel throughout the US. Currently we are using Cognito Forms but needed something more powerful that will allow us to track orders, drivers, their trailers and loads / equipment that they are hauling.

    I'm posting an image of my relationships, and I'm hoping maybe someone can look and tell me if it looks good or if there is a few other things I can do to improve it's efficiency before I move on.

    Here is some information that my make things easier to understand:

    A driver is assigned a truck. Each truck is assigned a trailer and for every order, each trailer is assigned a load / equipment to haul. The driver will call us to order permits for his current load. There is a starting address, where the driver picks up a new load load, and an ending address, where the driver delivers the load. Based on the starting and ending address, a route is created to avoid any restrictions, such as bridges that are too low for the truck to pass through, or the overall weight is too much for a bridge to with stand when passing over it. In some cases a pilot car or police escorts may have to be used to escort and survey the parts of the route.

    A driver might have a different truck if a new one is purchased. A different trailer may be assigned in order to accommodate the load. And a different load maybe assigned as well, but in all of these cases, it would be rare. In most cases it's the same driver, with the same truck, trailer and load.

    The orders table is the main table that encapsulates all the information together. There is only one driver, truck, trailer and load per order. There are two address per order and can be one to many states per order.


    Some questions that crossed my mind:
    Is it better to use the orders table to create a query that shows the relationships between the driver, truck and trailer or should I include a truck and trailer ID in the driver's table. We want to be able to show which driver currently has the truck, and trailer assigned to them. I'm just not sure which was is more efficient.

    One problem I have come across is with the starting and ending addresses. We have all the address in one table, but when I was setting up the relationships it seemed to duplicate the address table... and even so, when I create a form or run a query, I can't get it to show both, the starting AND ending addresses. How do I fix this?

    After typing all this out, I realize I totally forgot to put in any tables for pilot cars and police escorts! So I'll need to fit that in somewhere. These are issued per state and order. Not sure how to link them yet, but I'll be thinking about it.

    After everything is finalized, I'll be creating and easy to use form that will allow the permit agent to enter all this information in.

    I know there's a lot of information here, so I really thank anyone who has taken the time to read through it and help me out.

    Any additional tips or advice is greatly appreciated it!



    Thank you so much!

    Link to screenshot of relationships:
    https://1drv.ms/u/s!Auf81kbPTMG1gYFemmvhmxkWtLDTCA

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only relationship that might be improved is Orders to States.

    Table States should be lookup source for selection into a table OrderStates. In other words, the current States table would be better named OrderStates.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    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
    "but I'm still having some trouble warping my head around designing the database."
    There are several articles in various formats in the Database Planning and Design link in my signature.

    I recommend the tutorials from RogersAccessLibrary mentioned in the link. Work through 1 or 2 of the tutorials, then take what you have learned and review your tables and relationships. Create some test data and some test scenarios and action same against your model. Here is a link to "stump the model" which can be much more efficient than trying to work with a physical Access database. Easier to change things on paper before getting too deeply involved in Access.
    Once you have a proven, vetted model, you have a blueprint for your database.

    It's better to use only alphanumeric values for table and object names ---Access balks with special characters (such as #).
    The BA_Experts tutorials/videos are great for analysis concepts and ideas if you need to clarify processes, data input/outputs, gather details...
    Good luck with your project and welcome to the forum.

  4. #4
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Quote Originally Posted by June7 View Post
    The only relationship that might be improved is Orders to States.

    Table States should be lookup source for selection into a table OrderStates. In other words, the current States table would be better named OrderStates.
    Thank you so much for the advice!

  5. #5
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    I really ap

  6. #6
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Quote Originally Posted by orange View Post
    "but I'm still having some trouble warping my head around designing the database."
    There are several articles in various formats in the Database Planning and Design link in my signature.

    I recommend the tutorials from RogersAccessLibrary mentioned in the link. Work through 1 or 2 of the tutorials, then take what you have learned and review your tables and relationships. Create some test data and some test scenarios and action same against your model. Here is a link to "stump the model" which can be much more efficient than trying to work with a physical Access database. Easier to change things on paper before getting too deeply involved in Access.
    Once you have a proven, vetted model, you have a blueprint for your database.

    It's better to use only alphanumeric values for table and object names ---Access balks with special characters (such as #).
    The BA_Experts tutorials/videos are great for analysis concepts and ideas if you need to clarify processes, data input/outputs, gather details...
    Good luck with your project and welcome to the forum.
    I really appreciate that! I'll check those out. I've been watching tutorials on Lynda.com, which helps some, but the problem I keep coming across seem to be so specific. Even though I might find the internet saturated with the same question, it always seems slightly different than what I'm trying to do.

  7. #7
    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
    Can you post a copy of your evolving database (zip format)?
    Do you have a more detailed description of the business involved --sufficient to provide the scope of the project?

    We often see StartPoint, EndPoint or Destination and Waypoints for those interim stops/cities etc. May not be relevant but for consideration.

    Does a Order/Shipment/Voyage involve 1 driver, 1 truck and 1 trailer ?
    Can you change any of these during the trip?
    Just trying to flesh out the bigger picture.

  8. #8
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Quote Originally Posted by orange View Post
    Can you post a copy of your evolving database (zip format)?
    Do you have a more detailed description of the business involved --sufficient to provide the scope of the project?

    We often see StartPoint, EndPoint or Destination and Waypoints for those interim stops/cities etc. May not be relevant but for consideration.

    Does a Order/Shipment/Voyage involve 1 driver, 1 truck and 1 trailer ?
    Can you change any of these during the trip?
    Just trying to flesh out the bigger picture.

    Thank you for the response! I checked and one order will ALWAYS be 1 driver, 1 truck, 1 trailer and 1 load. Never anything different. However, a route may change, or more permits may need to be ordered to the order during the driver's trip.

    Here is the database file, it's mostly empty, but I think I have the tables and the relationships figured out.
    SampleDB07222019.zip

    I'm guessing I'll find out once I start building the forms, which is what I'm doing now. If anyone has anymore information that maybe helpful, I really appreciate it!!
    Last edited by jsmccloud; 07-22-2019 at 04:13 PM. Reason: Additional info

  9. #9
    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
    After reviewing the thread, I think this summarizes the "business facts".

    You are a permitting service that issues permits to truck drivers that haul oversize and/or overweight Loads that need permits to travel throughout the US.

    You need a utility that will allow tracking of orders, drivers, their trailers and loads ( equipment being hauled), route and permits at a detail level.

    A driver is assigned a truck
    A truck is assigned a trailer
    A trailer is assigned to a load / equipment to haul
    A Load is delivered by a Trip
    A Trip has a StartPoint and Destination
    A Trip is designed to avoid incompatible structures(low bridges/weight limits..)
    A Trip may have 1 or many routes
    (
    A altered route/segment will involve 2 or more waypoints identifying a route change)---for consideration
    A Route may be altered during the Trip
    A Trip may need 0 or many Pilot cars
    A Trip may need 0 or many Police escorts
    A Trip involves 1 or many States
    A State may require 0, 1 or many Permits
    A Load may require 0 or many Permits (depending on the Load and route/route change)
    A Permit is issued by a State
    An Order consists of 1 Driver, 1 Truck,1 Trailer and Load and Delivery


    I have inserted a Trip entity to indicate the proposed high level route/path between StartPoint and Destination. Since you indicate there is a StartPoint and a Destination(Delivery Location) and that the actual route may be altered during delivery, consider that a Trip may involve 1 or many routes(or sub-trips/routes).

    It sounds very much that an Order is for Haulage/Delivery of a Load, and there are no multiple drop-off locations as there often are in a typical Delivery construct. Also, it seems you always have a uniform, single item load. Is it possible that a single load could involve more than 1 Item? ( A sample of what constitutes a load would be helpful).

    I would not start working on forms at this point. You may want to mock up some samples reports and/or queries that you expect this database to produce/handle. Then create some test data and test scenarios and exercise your data model (tables and relationships) to ensure you can store, query and output the information you expect from this system. Adjust the model as necessary and get others involved to test the model. See "stump the model". Once you are convinced the model fully supports your needs, use that model to create the database. Getting your tables designed to meet your business facts is critical.

    Feel free to adjust the business facts as necessary. I was just trying to identify where Route, Permit, Escorts etc fit. My interpretation may be incorrect since I am not in your "business".

    Don't get into detail physical Access forms, queries, reports etc until you have confirmed your data structure. Easier to do with pencil/paper or white board etc than with a physical database.


    Good luck.
    Last edited by orange; 07-23-2019 at 05:28 AM. Reason: spelling

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    No need for 2 addresses tables. Start and End points simply refer to different addresses in same Addresses table!

    Are you sure always only a single equipment unit is moved per order/trip? In case an order is for several different units, you need an OrderDetails table (OrderDetailsID, OrderID, EquipmentID, Qty). In case always only one kind of equipment is transported, but there may be several units of it, a quantity field in Orders table will do.

    You will need States table where all states are registered, and an OrderStatePermissions table instead of current States table. This new States table will be source to select state for entries in OrdeStatePermissions and Addresses tables.

  11. #11
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Quote Originally Posted by orange View Post
    After reviewing the thread, I think this summarizes the "business facts".

    You are a permitting service that issues permits to truck drivers that haul oversize and/or overweight Loads that need permits to travel throughout the US.

    You need a utility that will allow tracking of orders, drivers, their trailers and loads ( equipment being hauled), route and permits at a detail level.

    A driver is assigned a truck
    A truck is assigned a trailer
    A trailer is assigned to a load / equipment to haul
    A Load is delivered by a Trip
    A Trip has a StartPoint and Destination
    A Trip is designed to avoid incompatible structures(low bridges/weight limits..)
    A Trip may have 1 or many routes
    (
    A altered route/segment will involve 2 or more waypoints identifying a route change)---for consideration
    A Route may be altered during the Trip
    A Trip may need 0 or many Pilot cars
    A Trip may need 0 or many Police escorts
    A Trip involves 1 or many States
    A State may require 0, 1 or many Permits
    A Load may require 0 or many Permits (depending on the Load and route/route change)
    A Permit is issued by a State
    An Order consists of 1 Driver, 1 Truck,1 Trailer and Load and Delivery


    I have inserted a Trip entity to indicate the proposed high level route/path between StartPoint and Destination. Since you indicate there is a StartPoint and a Destination(Delivery Location) and that the actual route may be altered during delivery, consider that a Trip may involve 1 or many routes(or sub-trips/routes).

    It sounds very much that an Order is for Haulage/Delivery of a Load, and there are no multiple drop-off locations as there often are in a typical Delivery construct. Also, it seems you always have a uniform, single item load. Is it possible that a single load could involve more than 1 Item? ( A sample of what constitutes a load would be helpful).

    I would not start working on forms at this point. You may want to mock up some samples reports and/or queries that you expect this database to produce/handle. Then create some test data and test scenarios and exercise your data model (tables and relationships) to ensure you can store, query and output the information you expect from this system. Adjust the model as necessary and get others involved to test the model. See "stump the model". Once you are convinced the model fully supports your needs, use that model to create the database. Getting your tables designed to meet your business facts is critical.

    Feel free to adjust the business facts as necessary. I was just trying to identify where Route, Permit, Escorts etc fit. My interpretation may be incorrect since I am not in your "business".

    Don't get into detail physical Access forms, queries, reports etc until you have confirmed your data structure. Easier to do with pencil/paper or white board etc than with a physical database.


    Good luck.
    This is awesome! This is extremely helpful. I've started writing facts down like this for other information we'll need when I get to making reports that I hadn't thought about before. Thank you so much!

    This database was just suppose to replace one spreadsheet and it's so overwhelming to see how complex it's becoming. I'd like to try and avoid making things more complicated, and to me it seems like some situations would be extremely rare. This is my first database and I'm wondering, how difficult it would be to simply add something later? For instance.... we are contracted to work with one trucking company. I'm planning to just add their information, like their name and address on the top of invoices I generate before sending them out to the drivers, but what if in the rare case we start hiring other employees to take on another trucking company? I'd then have to create a company and an employees table and start making those connections. I think maybe it would be easy enough; create the tables, setup new relationships and maybe just paste the new values to all the existing orders, but maybe not. In any case the situation is very unlikely, so I'm kind of second guessing what I should do.

    Any thoughts on things like this? By the way, we've set a deadline for when the database needs to be active, which is one month from now. So far I've been working on this thing for a week and most of what I've done is just trying to figure out the relationships and their connections. Is this going to be enough time?

  12. #12
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    I've checked with my partner, she's the one that issues the permits for drivers and she said it will always be just one load/equipment per order. So I think that part is OK.

    No need for 2 addresses tables. Start and End points simply refer to different addresses in same Addresses table!
    Ah, I think I see what you mean. In the relationships window, when you add a table twice, is it simply an instance or a reference to the same table? Where it adds "_1" to the end of the table name? This is the same as making a connection to the same field twice?

    You will need States table where all states are registered, and an OrderStatePermissions table instead of current States table. This new States table will be source to select state for entries in OrdeStatePermissions and Addresses tables.
    I'm sorry but I'm a little lost to what you mean here. I have a Permits table now, which list all the states a permit is needed for an order. It has an OrderID field so when I go to the orders table, I can see all the permits the order relates to.. if that makes sense.... If that's what you mean, I think I already have that done.

    Just trying to get a clear understanding.

    Thank you so much for your help!

  13. #13
    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
    To determine relationships you have to understand the business and the business rules/facts. Too many think that by buying Access (or any DBMS software) the database(s) will be created magically. Not so. There are many concepts involved in relational database (independent of a specific software) and they are fundamental to any database.
    Go to the Database design link in my signature and work through 1 or 2 of the tutorials from RogersAccessLibrary.
    You will learn a procedure that you can use with your own project.
    Watch a couple of the BA-Experts "knowledge nuggets" videos to see some analysis techniques. Create a more complete description of your business.
    It is easier to design for a broader scope and then develop and implement based on your priorities. If you need to expand (multiple companies etc), you can do so because you know where it fits in the model. If something is likely extremely rare (but might just happen, and you want to account for it), then document what you were thinking; some of the rules; some limitations including why it wasn't done originally and save it for reference when any modification/enhancement is being considered.
    Get a data model prepared (paper/pencil/whiteboard or whatever*) and test that model with test data and test scenarios. Get others involved to test the model. When there is agreement on the model's suitability to meet the needs, develop the database.
    * we once had a corporate database model (paper table designs) pinned on some cloth walls with colored strings between the "entities". It was invaluable to get people involved and resolved some issues that arose between development teams.

    Whether there is sufficient time depends on several factors. Testers, designer/developer, who accepts.... You don't necessarily need the full system on day1. So identify priorities and focus on them while adhering to your model. There is nothing stopping you/someone from identifying requirements for individual reports; Forms; queries but these should be on what each should contain/deliver --not on final design in Access. Again it depends on expectations and priorities.

    Keep records of what you are doing; get others involved to ensure you are getting the big picture and constructive criticism; don't assume you or any one person understands all the "bits and pieces"; review your steps with what was laid out in requirements and priorities. Be very cautious of "scope creep" - it kills may projects.

    Can you give us a little more detail/example of what sort of things make up an order and Load?

    Keep the forum apprised of your efforts.
    Good luck.

  14. #14
    jsmccloud is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    14
    Thanks again for your input! I will go through the tutorials you suggested and continue to design the model.

    I'll keep the forum updated!

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Can you give us an example of a Route and the Permits required for that Route?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Which relationships to use in my design?
    By mbenton in forum Database Design
    Replies: 11
    Last Post: 07-18-2016, 03:51 PM
  2. help with database design and relationships
    By Jen0dorf in forum Access
    Replies: 8
    Last Post: 08-26-2015, 11:57 AM
  3. Database Design: Many-to-Many relationships
    By rmohaisen in forum Database Design
    Replies: 11
    Last Post: 02-13-2014, 12:23 PM
  4. Replies: 2
    Last Post: 12-14-2012, 02:53 PM
  5. Database Design/Relationships
    By sloft21 in forum Access
    Replies: 1
    Last Post: 10-31-2012, 09:07 PM

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