Employee Travel Database

[mikecohn]

Hi all,

First-time poster, long-time reader.

I'm endeavoring to make a small database for my Field Service dept. I'm getting stuck in a few places, but I was thinking I could show you all my tables/fields and let you pick at them, and then maybe move on from there.

As a background, I have taken some limited Access training courses and can bull my way through much of the program, but there's still a serious n00b knowledge gap.

Anyway, here's my premise: My company builds large-scale electrical equipment and my field guys maintain/repair said equipment. We "file" each job as a Shop Order. Over the years we'll take multiple trips out to different Shop Orders depending on need. We make flight reservations, hotel reservations, and car reservations. We'll go to the same Shop Order location several times if needed. Sometimes we're paid to build the SO on the field, sometimes we make warranty repairs.

My database would track all relevant travel information and ultimately be able to generate reports on paid vs warranty expenditure, while just keeping a decent record of hotel/airline preference, etc.

I've attached an image with my relationships and tables/fields. I've already started messing with forms, but feel I'm getting too far ahead of myself.

Feel free to ask clarifying questions and critique away. I'm starting with a broad request for insight and then I'll start getting into my form based questions.

 

[Mihail]

Why you have the field EmployeID in tblTripInfo ?
I think that a trip has nothing to do with any employe.

Take a look to the relationships.
Do you see a closed chain ? It is because that field.
So, remove it from tblTripInfo. The rest seems to be OK.

BTW:
Try to arrange all the tables in Relationships Window with ONE to the left and MANY to the right.
If you can't, a great sign for question should raise: something is not ok.

Good luck !

 

[plog]

Nothing sticks out as horrible.

1. In tblFlightInfo you have 4 Date/Time field pairs (Depart, Arrive, RDepart, RArrive). The field type is a called 'Date/Time" for a reason--it can hold both pieces of information in one field--you only need a total of 4 fields not 8.

2. Actually you only need 2 of those fields in tblFlightInfo. I don't think RDepart/RArrive should be in tblFlightInfo. If they are to catch a return flight home, that data should be a new record in the table (its going to have its own flight number and From/To data).

3. EmployeeID shouldn't be both in tblPurchInfo and tblTripInfo. It's redundant to have it in both places. I'm not sure where it should be, but it shouldn't be in both.

 

[mikecohn]

Thanks for your input, folks. I've taken EmployeeID out of tblTripInfo and slimmed down tblFlightInfo.

So here's my next catch: I know I'll have a different TripID for every trip, that's a given. However, many trips will be to the same site. (For example, travel to fix Shop Order# 12345 happened three times in March '13.) I'm worried about there being too many redundancies in the ShopOrder field. Would I want to make a Shop Order table?

Moving on from here, I'm trying to imagine an intuitive form design that will populate the necessary fields all together, since I won't necessarily be the end user. I'm aware I'll need to build subforms and such.

Should I propose my questions here, or should I start a new thread in the forms section?

 

[Mihail]

For example, travel to fix Shop Order# 12345 happened three times in March '13.

I can't see any problem here.
Of course, I don't know the whole business.

Moving on from here, I'm trying to imagine an intuitive form design that ...

As someone said few days ago, to design forms is "sexy" but to design properly relationships is most important.

If you have not a good DB at tables level, you will never end with the "sexy" work.

Should I propose my questions here, or should I start a new thread in the forms section?

I think is better to stay here.
But, from time to time, post a new pic with your Relationships window.

 

[plog]

What redundancies? Can you provide sample data from tblTripInfo illustrating what you mean?

 

[mikecohn]

For example:

TripID......ShopOrder.....Warranty
.. 1............50000............x
.. 2............40000............
.. 3............87655............
.. 4............50000............x

The technician went to the same job-site ("50000") twice in this illustration. Does it matter that I'll have several redundancies like that? Should I just make a table that with a ShopOrder field and a PK? I ask this because I initially had a table where I would log those ShopOrders, as PK, but I was worried I was normalizing "too much"...

Uploaded my newest relationships, for your viewing pleasure. Does this look like a good start? Now I want the TripInfo (What shop order is this trip for? Is it Warranty or Paid?) as the main form, and then somehow include the rest of the forms inside of that. As in, one single trip for one single shop order would potentially include purchasing a flight, a hotel, and a car rental.

Sorry if these are garbage explanations of my needs.

 

[jdraw]

Can you tell us about the business?

Your tables and relationships should support your business.

Here is a sample of the sort of info that would be useful.

From Rogers Access Library

Narrative
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

 

[plog]

If Warranty and Paid are always the same and tied to the ShopOrder, then yes it should be its own table and you should have a junction table between the shop order data and the tripinfo.

 

[Mihail]

I said:

Of course, I don't know the whole business.

JDRAW said:

Can you tell us about the business?

My English is not the best one, but still I understand that, if someone ask for information about the "business" in a databases forum, he don't like to know how many employes you have, theirs salary etc.

Is not the same if you have a list with "fabricated" travels:
Travel_1
Start: Monday 05:00 AM
Airport: 06:15 AM
Flight no: 20125
FlightStart: 07:00
Bucharest (Otopeni): 10:00 AM
Rent a car
Hotel "Astoria"
Visit the "Peoples House"
Tuesday : ..............................

Travel_2
Start: Monday 05:00 AM
......
Cairo
......
Athena
......

or you sell travels "at demand"
Customer say:
My holiday start is 25 november
I like to intermediate for me a trip in order to see this objectives:
1) The Great Pyramid
2) The Great Lakes
3) The Kremlin museum
etc

In the first case you will have a table with trips, prices etc
In the second case you will create, for each trip, a record, you need to find hotels, cars, aircraft, camels etc, to calculate the price and so on.

So, TELL US, in plain English, ABOUT THE BUSINESS !

 

[mikecohn]

Mihail, I believe I gave a pretty decent explanation of the business in my first post, but I'll try and repeat it more clearly for you. I don't know where this "employees and salaries" thing is coming from, though. I think you might be getting lost in translation, perhaps.

Anyway. Like I said before, My company manufactures, installs, and repairs (when needed) large format electrical equipment. My department in particular, Field Service, takes trips to install or repair the equipment (We number them by Shop Order, i.e. 50700, 12835, etc.). I would like to keep track of the trips Field Service makes, especially the expenditure. I would like to compare warranty expenditure over paid installation expenditure.

This database in the future would also tie into our main Field Service database to generate more detailed reports not having to do solely with travel.

Plog, thanks for your suggestion. Another clarifying point: Warranty and Paid are not always the same. For example, I could be paid to work on S.O. 50000 to install, and a few months later have to make a trip to install a piece of equipment that was found to be missing. Still go-ahead and make the table? If so, what, theoretically, could be my junction?

 

[plog]

You've lost me. If Warranty and Paid can vary by the trip, then that's the level you need to store them at. If they are tied to a Shop Order, then that's the level they should be stored at.

You were worried about redundant data and in your sample data (Post #7) you listed Warranty as being the same with every ShopOrder. With your latest post, it seems that Warranty isn't redundant data as it could vary by trip.

If you still need help, post a new image with your proposed junction table breaking out ShopOrders. That will help me get unlost.

 

[mikecohn]

Yikes, I think I see what I did there. My sample data wasn't up to par.

I'm with you on Warranty and Paid, they're going to stay in TripInfo, because they vary by trip.

However, the same Shop Order will pop up in multiple different trips. As in, Trip 1 and 4 were both to the same Shop Order, therefore that Shop Order was repeated twice. Instead of repeating it in the TripInfo table, would I want it as a single primary field in a separate table?

 

[plog]

If ShopOrder is the only piece of data in the ShopOrder table, you don't need a whole table for it. If you want to store more data along with ShopOrder, then you need a seperate table, but you don't need one for a single field.

 

[mikecohn]

Thanks for your help.

Is there anything else I should complete before I begin to tackle forms?

 

[jdraw]

Do you have test data? Have you used your model and test data to see if the model supports your business? Before starting forms etc, I'd suggest playing "stump the model".
Use test data and your business rules, work out your processes on paper. Are there any errors or inconsistencies? Reconcile every issue either by changing the model, adjusting the test data. Work through this loop until thee are no inconsistencies.
You'll be surprised how much you'll learn about your business details.

Good luck.

 

[plog]

Agree with jdraw. In the same vein--Reports should come before Forms. Throw some test data into your tables and see if you can create the reports you will need. That's also a good way to test your data model.

 

[mikecohn]

Thank you all. You've really been a good help. I'll input the data I've collected over the last few months and play around with it.

 

[jdraw]

I don't think you should input all of your collected data at this time. I was suggesting 5-10 test records (good and bad data); work these against your model and make sure your test data and model "work". Once you have resolved any issues with data and/or model, then go to your reports (output you want) and then to forms (user interaction). When these work with your test data , then do mass load of collected data by whatever tools are appropriate.