need help with table design

[pbmgoat]

Ok, I am fairly new to database design so bear with me.

I am working on a database on Transportation. My main concern is with the relationships, more specifically with Employees. I have a main table Employees and it contains basic information about the employee. I also have a field stores SSN and another that stores an EmployeeID. Now my question is with some tables linked to it.

The tables are as follows:
Users - for certain employees to log in into the system with a username and password
Drivers - not all employees are drivers but is linked with EmployeeID to Employees
Shifts - the working schedule for drivers ONLY (linked to Drivers)

There other tables which I have not linked because I thought there were too many linked to the EmployeeID

Complaints - for complaints that are called in against a driver
Accidents - for accident records
WriteUps - write ups against an employee
Incidents - incidents that occur in the vehicle
Vehicles - the vehicle that the driver uses

If I link the relationships, there will be about 10 tables with EmployeeID as a foreign key. Would that be alright to have that many tables linked?

This database is large as there are other aspects of this database. Billing(Bookkeeping), Payroll, Trips, Vehicles/Vehicle Maintainance/Vehicle Equipment.

As I go along with the design I will probably have more questions, but this will be it for now.

Thanks,
JC

[SheSaidImaPregy]

I would recommend combining a lot of those tables together, but it depends on how far you are going within those tables (how many columns).

You can link complaints to writeups into one table, as well as accidents and incidents. You should really think about doing this, as it will save a lot of database room (if that is a situation you need to avoid). And also it is quicker to retrieve and update information, using one query instead of two (if not using T-SQL).

[trudge]

Generally, you want to create tables that consist of data about one particular object or transaction. I don't know if there is such a thing as 'too many' relationships. The design of your database is driven by your need to access the data in certain ways. In this case, I don't see a problem with your design. You have various tables linked to a particular driver by their ID.

What you want to try and avoid is duplication of data (ID numbers are not data). For example, you don't want a driver's name appearing in more than 1 table. Also, any data that can be calculated from other data is generally not stored.

Look into normalization - that is the process that will help you weed out design problems. I usually try and shoot for a 3NF (third normal form).

[im.thatoneguy]

I don't think he can linke Accidents and Incidents. I assume Accidents are formal (reported to the state patrol) accident accidents, while as incidents are like "I spilled a gallon of coffee on the passenger seat" or "cargo shifted during transport and damaged rear door".

You would probably have completely different columns for them just like a writeup and a complaint are different. A writeup tends to be a reprimand after an investigation/interview while a complaint could just be someone calling in and saying "Truck#36 was driving too slow!"

I know very little about database design myself. But logically your db base sense to me for the most part. Now I don't know how your data is layed out within those tables but it seems to me like you need a vehicle table (unless a driver ever only drives one vehicle) so that you can put in a time/date time frame for:

Shifts:
Driver | StartTC | EndTC | Vehicle
2343 | 23 | 43 | 1

So that if a complaint is called in about Vehicle 1 at time "35" (the universe has been around for 35 seconds :))...

...your Complaints table would be:
Vehicle | Time | ComplaintString
1 | 35 | The driver looked unshaven and dirty.

Instead of actually storing the driver information (How would the caller know who the driver was.) It would store all the information reported in the call.

You could later then determine who the driver was based on shift information. And if a driver perhaps asked someone else to fill in for his shift you could update the shift information alone later and have the complaints list updated accordingly ("where were you on the night of the fifth!?")

[pbmgoat]

thanks for the replies, I am still working on the design. As soon as I can I will probably post a screen shot of the relationships.

I know very little about database design myself. But logically your db base sense to me for the most part. Now I don't know how your data is layed out within those tables but it seems to me like you need a vehicle table (unless a driver ever only drives one vehicle) so that you can put in a time/date time frame for:

Yes, I have a vehicles table that stores vehicle information. A driver usually drives the same vehicle unless there are problems with it then they will use a spare or someone elses's.

As far as SHIFTS, a driver does not have a straight through shift. They work in the mornings for a period of time, mid-afternoon, and then the afternoons. How would I go about designing a table for that situation?

One question, for the complaints table I was also thinking of making it for complaints, questions, comments, suggestions, and concerns all in one table but what would I name the table? Any ideas?

[im.thatoneguy]

Well as far as shifts go it seems to me like the only data you would need would be:

Shifts:
Driver | Vehicle | StartTimeCode | EndTimeCode

I'm not sure how you would handle recurring shift schedules though. Such as Bob drives truck #13 every thursday from 5am till 8am. Or is it a scheduling system that's always changing depending on the day more than regularity.

How are you handling Timecards now?

---

How about calling them all "reports". That sounds generic enough to me. Or else it could be "feedback".

[pbmgoat]

One problem I have encountered is with the Vehicles table.
There are some tables that are related to this table, for example Accidents, Departments, Shifts, VehicleMaintainanceRecords.

The primary key to Vehicles table is the Unit#. Now say for example one of these vehicles get totalled or is replaced with a brand new vehicle and we use one of the old unit numbers to a vehicle that has not existed in quite some time. When we lookup accident history on a unit number or look at maintainance records on a unit number, we will not see the correct history.
For example say Unit# 50 had been used on a 1998 Ford F250 many years ago and that vehicle was totalled and say many years down the road we get a new vehicle in and use Unit #50 on a 2009 Chevy 2500. We will no longer have information on the Ford only the Chevy.

I hope I didn't confuse anyone with what I am trying to say.

Thanks,
jc

[im.thatoneguy]

You'll want a Vehicle GUID in addition to the Vehicle ID.

You could add a Timecode to it as well.

So:

VehicleGUID | VehicleID | BeginService | EndService
F22415 | 51 | January 1 2002 | December 31 2006
A4891D | 51 | January 1 2007 |

If Endservice == "" then Endservice = getdatefunction()

It seems like that should do it.

That should have enough information then that if yo ucall up an accident report on say

January 5 2004 you could SQL something like

select in Vehicles where VehicleID = 51 and where beginservice is <= January 5 2004 and where endservice >= January 5 2004.

[SheSaidImaPregy]

I can help you a ton, as I know database design very well. However, I need information about what is going to go in each table. Please include everything, payroll and the rest. I can design you it fairly quickly and explain it to you. So far, this is what I would presume you would need:

Users - for certain employees to log in into the system with a username and password
Drivers - not all employees are drivers but is linked with EmployeeID to Employees
Shifts - the working schedule for drivers ONLY (linked to Drivers)

There other tables which I have not linked because I thought there were too many linked to the EmployeeID

Complaints - for complaints that are called in against a driver
Accidents - for accident records
WriteUps - write ups against an employee
Incidents - incidents that occur in the vehicle
Vehicles - the vehicle that the driver uses

Drivers
- EmployeeID ( int ; Foreign Key ) ( Delete )
- DriverID ( Primary ; Auto-Incr )

Shifts
- ShiftID ( int ; Primary ; Auto-Incr )
- ShiftStart ( DateTime )
- ShiftEnd ( DateTime )
- DriverID ( int ; Foreign Key ) ( No Action )
- VehicleID ( int ; Foreign Key ) ( No Action )

Accidents/Incidents
- RecID ( int ; Primary ; Auto-Incr )
- IDType ( char(1) ) *Accident or Incident. You should use A or I*
- VehicleID ( int ; Foreign Key ) ( No Action )
- DriverID ( int ; Foreign Key ) ( No Action )
- RecDate ( DateTime )
- DateCreated ( DateTime ) ( getDate() )
- Report ( Text )
- Explanation ( Text )
- OtherDriversLicense ( varchar(20) )
- OtherDriversName ( varchar(80) )
- OtherDriversReport ( Text )
* Obviously there's more, but that's where you come in *
* You can combine those two into the one above. Your *
* Code will separate it out. That or split them. Same *
* With the next two tables (Complaints & WriteUps) *

Complaints
- ComplaintID ( int ; Primary ; Auto-Incr )
- ComplaintDate ( DateTime )
- DateCreated ( DateTime )
- EmployeeID ( int ; Foreign Key ) ( No Action )
- AdministratorID ( int ; Foreign Key ) ( No Action )
* This is for the person who took the complaint. *
- Complaint ( Text )
- ExpectedSolution ( Text )
* Again, there's more but that's where you come in *

WriteUps
- WriteupID ( int ; Primary ; Auto-Incr )
- WriteupDate ( DateTime )
- DateCreated ( DateTime )
- AdministratorID ( int ; Foreign Key ) ( No Action )
- EmployeeID ( int ; Foreign Key ) ( No Action )
- Writeup ( Text )
- Explanation ( Text )
- Resolution ( Text ) * Warning Explanation *
* more... *

Vehicles
- VehicleID ( int ; Primary ; Auto-Incr )
- PlateNumber ( varchar(10) )
- VehicleDescription ( Text )

VehiclePast
- VehicleID ( int ; Foreign Key ) ( No Action )
- DriverID ( int ; Foreign Key ) ( No Action )
- RecDate ( DateTime )
- DateCreated ( DateTime )
- Description ( Text )
- Severity ( char(1) )
- Repaired ( char(1) ) ( y/n )
- DateRepaired ( DateTime )
- RepairCost ( real )
* This is for when a driver hits something, you can *
* keep track of the vehicles past *

Something like this? It can be condensed much more and can actually be "cleaner", however, I would need your description and columns you would like in each table. Then I can give you a true relational database.