•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 374,175 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,489 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 688 | Replies: 8
 |
•
•
Join Date: Feb 2007
Posts: 4
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
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).
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).
•
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 174
Reputation:
Rep Power: 1
Solved Threads: 20
Junior Poster
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).
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).
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
"Others make web sites. We make web sites work!"
•
•
Join Date: Feb 2008
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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!?")
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!?")
Last edited by im.thatoneguy : Feb 10th, 2008 at 4:43 pm.
•
•
Join Date: Feb 2007
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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?
•
•
•
•
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:
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?
•
•
Join Date: Feb 2008
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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".
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".
Last edited by im.thatoneguy : Feb 18th, 2008 at 11:46 pm.
•
•
Join Date: Feb 2007
Posts: 4
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Feb 2008
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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.
Last edited by im.thatoneguy : Feb 21st, 2008 at 3:07 pm.
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation:
Rep Power: 3
Solved Threads: 61
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:
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.
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 *
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
•
•
•
•
avatar backup breach business creative daniweb data data protection database deleting records from ms sql table where columns have duplicate values design europe government gpt guid partition table hacker howto hp ibm illustrator medicine navigation news normalization partition photoshop print research security server sql survey toread tutorials web
- Need Freelance CSS expert to recode existing table design (Web Development Job Offers)
- Help on General Best Practices for Table/Database Design (Database Design)
- Multi Location Store Software database design (VB.NET)
- Help with contact/mailing list database design... (Database Design)
- New Table Design (Database Design)
- database table design problems (Database Design)
Other Threads in the Database Design Forum
- Previous Thread: hospital management system
- Next Thread: I want to know how to make a Reviewing Database.
