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