Relationships

[bopsgtir]

Hi guys, another newbie here,

Im after some help relating to relationships and what raw data i would need.

Here a brief of what im trying to do. i run a telecommuncations team in London and have 150 employees, im currently expanding at about 2 techs per months and obviously have leavers. i bits of information i want from my data based is firstly a timeline of what techs were in what van and when so if there is any damage i can track it back so also a timeline relating to the van reg aswell. also the guys are given a full tool kit and have monthly audits. i need to know if any tools need to be replace for wear and tear or from lost or abuse for which the tech gets charged, also the guys are given pdas but these are always breaking down, these are identified by imei number, i need to know when the pda comes to site and what tech has it and when it is sent back, as they sometime lose track there end if they ever gave me an imei number i could say if we ever had what techs had it between what dates.



Ive attached a copy of the relationships, what im after is someone to have a quick look to make sure it makes sense and if there is anymore information that i would need

[jzwp11]

I took a look at your structure and had a few suggestions.

Regarding the vans, if you are tracking all the techs in the van and the damage done while a van is in the possession of the techs, then your current structure would not allow that. Perhaps this:


First a table to hold when the van was assigned to a group of techs

tblVehicleAssignments
-pkVehAssignID primary key, autonumber
-fkVehicleID foreign key to your Vehicle table
-DateOut
-DateIN

Now a table to capture the techs assigned to the vehicle

tblVehicleAssignTechs
-pkVehAssignTechID primary key, autonumber
-fkVehAssignID foreign key to tblVehicleAssignments
-fkTechID foreign key to your technician table

tblVehicleAssignDamage
-pkVehAssignDamageID primary key, autonumber
-fkVehAssignID foreign key to tblVehicleAssignments
-txtDamage
-currCost

With regard to your mobile phone, tools and PDA tables, essentially they are all tools that are assigned to the tech and when a tech leaves, these tools can be reassigned to another tech. Since they are all tools, why not put them in 1 table.

tblTools
-pkToolID primary key, autonumber
-fkToolTypeID foreign key to a table that holds various tool types: PDA, Mobile phone, screw drivers etc.
-txtToolMake
-txtToolModel
-txtStatus
-currPrice

Now a table to relate tools to the techs

tblTechTools
-pkTechToolID primary key, autonumber
-fkTechID foreign key to your tech table
-fkToolID foreign key to tblTools
-SimCardfield (assuming that this may change when a phone is reassigned)
-dateIN
-dateOUT

Technically speaking, you have two dates related to a tech/tool combination (or a vehicle assignment) which really describes a one-to-many relationship which would require a separate table to hold the dates. Some people may see this as going to the extreme normalization and others not.

For the fully normalize treatment, tblTechTools would change to the following & a new table added.

tblTechTools
-pkTechToolID primary key, autonumber
-fkTechID foreign key to your tech table
-fkToolID foreign key to tblTools
-SimCardfield (assuming that this may change when a phone is reassigned)

tblTechToolsTransactions
-pkTechToolTransID primary key, autonumber
-fkTechToolID foreign key to tblTechTools
-dteActivity (date field)
-Activity (two choice IN or OUT)


The same could be said for the tblVehicleAssignments as well with its 2 date fields. A more normalized version would be as follows:

tblVehicleAssignments
-pkVehAssignID primary key, autonumber
-fkVehicleID foreign key to your Vehicle table

tblVehicleAssignmentsTransactions
-pkVehAssignTransID primary key, autonumber
-fkVehAssignID foreign key to tblTechTools
-dteActivity (date field)
-Activity (two choice IN or OUT)