Constructive Feedback Needed: Database Narrative

[CharissaBelle]

Everyone on this forum has been extremely helpful. Last week someone asked me for my narrative and data model which I had never heard of before. I have been working on writing this and would love some feedback. I'm tired of writing and want to start modeling, but I realize the importance of laying a good foundation.

Here's what I have:



We are a trucking company which exclusively does contract work with the USPS. We are looking for a database system where we can store all the information related to our business (contracts, drivers, vendors, post offices, etc.) and look up that data in an organized way. We would like to be able track phone calls we receive. Also track the schedule of trips a driver takes. We will also need the trip information like who took it, mileage, fuel, etc. so we can report to the government.

A contract is a an agreement between our company and the USPS that we will do certain trips. Each contract has multiple trips which go to multiple post offices. It is designated by a unique 5 digit identifier made up of numbers and letters. Some contracts also have seasonal contracts. A seasonal contract is a sort of child contract of the regular contract.

A post office will issue extra trip paperwork when they add a stop or trip that isn’t a part of the contract. If a trip is held up by the post office causing it to leave late, they will issue a late slip. Late slips and extra trip paperwork needs to be tracked so we can insure we are billing correctly.

We break all of our contracts up into the individual trips and put together trips to form a bid. We try to put trips together to equal about 40 hours per week, so a bid is a full time position a driver can have. A full time driver is called a bid driver because he has a bid. We also have part time drivers to fill in when a bid driver gets sick.

We have 9 terminal cities. A terminal city is the city that the bids, trips, and drivers are based out of. There is a yard or someplace we park our trucks in each terminal city.



We own most of the equipment (trucks and trailers) we use. Though, some trailers belong to the post office and we are leasing 6 trucks from Ryder in the Las Vegas/Reno area. During the seasonal contracts, we rent more trucks and trailers from many vendors. We use other vendors to fix our trucks and trailers.

[CJ_London]

It looks like a good start - there are some areas which need further clarification before you start designing your tables

Some things to expand on (no particular order)

contracts, drivers, vendors, post offices, etc - etc?

We own most of the equipment (trucks and trailers) we use - do you need to build in maintenance schedules?

A post office will issue extra trip paperwork when they add a stop or trip that isn’t a part of the contract - how? email? phone/fax? your documentation or their documentation?

A full time driver is called a bid driver because he has a bid. We also have part time drivers to fill in when a bid driver gets sick. - permanent employees or contract or a mixture? (how to track availability)

also to do with what type of db setup required

Are all users in one office or distributed?
Do you have a common network or need to create one?
Or do you require a web based backend and/or front end?
How do you communicate with drivers when on the road? voice/text/email/other?

[CharissaBelle]

also to do with what type of db setup required

Are all users in one office or distributed?
Do you have a common network or need to create one?
Or do you require a web based backend and/or front end?
How do you communicate with drivers when on the road? voice/text/email/other?

Thank you Ajax. I'll add the following paragraph.

"The db will only be used by office staff and dispatchers. Office staff can access everything. Dispatchers need to be restricted from seeing personal information about drivers such as address and social. We are all in one building and on the same network. Our server is divided into different drives I think. Right now we have an old DOS based db that is split between an L: drive and M: drive."

I understand the importance of these questions except the last one. We communicate by phone for the most part, but also are collecting drivers emails so we can communicate that way for things such as the monthly newsletter and sending trip schedules. Would this need to go in the narrative?

[CJ_London]

I understand the importance of these questions except the last one. We communicate by phone for the most part, but also are collecting drivers emails so we can communicate that way for things such as the monthly newsletter and sending trip schedules. Would this need to go in the narrative?

It's about day to day communication - I was assuming that for example the 'extra trip paperwork' issued by a post office needed to be communicated to the driver as a change in schedule. Drivers could also send a text or email regarding late slips or to say that pickup/dropoff has been completed to mark their progress. Perhaps not as friendly as a phone call, but something that can be automated and update the db without user intervention.

With regards including in the narrative, suggest it should be, even if it is 'communicate by phone' so it is clear how the function of the db communicates and integrates with other parts of the business. Presumably you need some sort of progress check that 'extra trip paperwork' has been communicated to the driver and acknowledged, even if it only a couple of checkboxes in a record somewhere?

[orange]

Do you have any info for Billing in your model?

Late slips and extra trip paperwork needs to be tracked so we can insure we are billing correctly.

You have a great start. In my view you need more complete definitions of the things and the processes.
However, it's a learning journey, as you will find out, and not an event so to speak.

Make a sample/mock up some forms, and/or reports or queries and make sure you have the fields in your tables to create the forms/reports/queries. Get detailed with this sort of test.

In your own mind, would you have enough information to start building this application --if someone handed you the information you have at this time? Work from basic principles-- think in terms of who, what, why, where, when , how and how often. Who does what? How often do they do it? What do they need to do it? How do you know it's complete..... These are questions to resolve with the model and test data and scenarios. You don't want to go through the design and populating effort; create forms; make them pretty and consistent; then find out the tables and relationships don't really meet the requirements.

Here's a free youtube video on analysis and design (it's more involved and detailed than necessary) but it might be useful to watch it and get a feel for what is involved. There are other videos, but it's just to give you an appreciation of things to ferret out the info in the business and the business processes.

Good luck.

[CharissaBelle]

It's about day to day communication - I was assuming that for example the 'extra trip paperwork' issued by a post office needed to be communicated to the driver as a change in schedule. Drivers could also send a text or email regarding late slips or to say that pickup/dropoff has been completed to mark their progress. Perhaps not as friendly as a phone call, but something that can be automated and update the db without user intervention.

With regards including in the narrative, suggest it should be, even if it is 'communicate by phone' so it is clear how the function of the db communicates and integrates with other parts of the business. Presumably you need some sort of progress check that 'extra trip paperwork' has been communicated to the driver and acknowledged, even if it only a couple of checkboxes in a record somewhere?

Okay, that makes a lot of sense. I hadn't even thought of the possibility of automating some things.

[CJ_London]

I hadn't even thought of the possibility of automating some things

It is more about how you want the application to interact with various parts of the business. As suggested in part of Orange's post

Who does what? How often do they do it? What do they need to do it? How do you know it's complete