best way to tackle table design

[tonycl69]

Hello, I have a table of drivers, driverid, unitid, drivername, begindate, and enddate.The unitid relates to a main table which have the vehicles, and the begin and end dates are when the driver had the vehicle and returned it. The reason I ask if this could be made bettewr is I have created a form with the main form the main table with the vehicles and a subform with the drivers table master and child as the unitid so that works fine. I need 2 things to happen, and maybe I am in the wrong place so please tell me to move on if I am. Firstly I would like to pull out the latest driver of a vehicle and place that on another form also related to a vehicle, and also would like to view a driver and find out what vehicles he was driving and when, a sort of reverse lookup instead of bringing a vehicle up and seeing who had been driving it. In my relationships I have a one to many on the vehicle side so one vehicle to many drivers, should I also have a one to many the other way so has to show one driver could have more than one vehicle and have the driver number in the main table as a one to many from the driver table? Thanks for you help however small it maybe, sometimes you look at these things so long you can't see the wood for the trees as it were.

 

[MarkK]

You need one table for each real-world "thing" or "event" you want to keep track of. In your case you seem to have a Driver thing and a Vehicle thing, but what table are you putting the BeginDate and EndDate in?
hth

 

[jdraw]

I think you will get useful information from the tutorial at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

It goes through the process of identifying the things you have, and how they are related and leads to a well normalized structure.

Good luck with your project

 

[tonycl69]

Thanks lagbolt, are you suggesting I place the dates into another table?
And yes jdraw I will take a look thanks

 

[jdraw]

I suggest you show us your proposed tables and relationships as you proceed.

Driver<------>Vehicle is many to many
You will need a junction table, to represent the combination of Driver and Vehicle, to resolve the many to many situation.

along this set up (DriverInVehicle could be Trip/Route/schedule)

Driver--->DriverInVehicle<----Vehicle

see this video for Many to Many
https://www.youtube.com/watch?v=7XstSSyG8fw

 

[tonycl69]

I think I have the understanding of the tables I need to problem I have now id populating them. My orginal table had everything in it ie driverid, unitid, drivername, begindate and enddate, and there are many entries for drivers. so I need to strip out of that what I need for each new table.

 

[jdraw]

You may be dealing with a number of dates. The EmployeeStartDate, EmployeeEndDate, VehiclePutIntoServiceDate, VehicleMaintenanceDate,VehicleRemovedFromServiceDate,etc.

It would help if you provided a 4-5 line description of exactly what your "business" is. Once you have such a description you should be able to determine what is in scope (part of your database) and what is not. If your design is a little more conceptual, you may be able to set it up to see where your database "fits" within other business processes and/or existing databases.

Good luck.

 

[tonycl69]

Thanks, ok here goes, we are plant and transport company, with several vehicles large small etc, I have succesfully created the main database to track inspections , services, MOT, Tax, maintenance, not a problem. As part of this we need to track who is driving what and when, in the main vehicle table I have the UNITID(PK), I then have the drivers table which I created many years ago, and in my early years as a self taught database learner I set it up with DRIVERNAME, UNITIDID(FK) to main vehicle table as a one to many, being one vehicle many drivers, also in this table I had DRIVERSNAME, MOBILENUMBER, STARTDATE and ENDDATE. Everything was working fine but was unable to do a reverse lookup, ie what vehicles the driver had had in the past and when, but I couldn't easil;y find what driver had in the past with creating a query every time. So I thought a revamp is neccessary then I ran into the problem, because the DRIVERS table is populated with unitid numbers and multiples of drivers and dates I'm afraid i have got into a mess, and for some reason can't see a way out, sometimes good to have another pair of eyes on it, if you know what I mean. I have kept the data in the table so you can see the duplicates as they should be kept. I trust your discretion.

 

[jdraw]

I'm not sure of what I am looking at. I see one table with some data.
Looking at one table independently is not really helpful.

These quotes of yours are part of your issues/requirements

we need to track who is driving what and when

what vehicles the driver had had in the past and when, but I couldn't easil;y find what driver had in the past with creating a query every time.

My view is you have a structure issue and that is what has to be addressed.

 

[tonycl69]

Thanks, but I know I have a structure problem, thats what I need help on, I need this to be independant to the main database the only link back is the vehicle. So that this will run independantly. Rather like a car rental company or vehicle hire database without the costing.

 

[jdraw]

There are free sample data models at
http://www.databaseanswers.org
these may be useful to you (ideas of structure)
http://www.databaseanswers.org/data_models/car_hire/car_hire_physical.htm

[business facts]
http://www.databaseanswers.org/data_models/vehicle_rental/facts.htm

The link I gave in post #3 should give a process to help get your tables and relationships correct to meet your requirements.

Good luck