So I have been building a DB for work and the primary purpose of it is to track Jobs. It is a relatively simple DB at this stage and I came across a situation where I don't know what the best option to do is and I would like anyone's opinion.
I have 3 tables
tblJobs, tblEquipment, tblOffice
Currently tblJobs has 2 fks: Office ID and EquipmentID
tblEquipment has 1 fk: Office ID
Well, when I was making tblJobs it didn't even dawn on me that I could just have one foreign key: EquipmentID and that record would reference the OfficeID. So if someone moved a piece of equipment from one Office (aka WorkCenter) to another Office that job would reflect that change because it references only fk EquipmentID. Since that new Office is the owner of that equipment they would be responsible for the job as well. That is the beauty about relational DBs right?
The only problem is a majority of the time when you open a job you do not know the equipment ID just the workcenter it is assigned to. So how would the tblJobs store the workcenter if there was no equipmentID to reference to the workcenter?
So I discussed this with one of my friends and we came up with 2 solutions. The first one is mine and it goes like this. I get rid of the fk Office ID in tblJobs which is how it should be, and just add Unknown Equipment ID records for each Office in tblEquipment. So if I had 10 Offices I would have 10 Unknown Equipment ID records in tblEquipment that reference those 10 workcenters. So if I add a job I could select a workcenter then select Unkown for the equipment which in turn has the equipmentID which tblJobs needs.
The second solution comes from my friend and it goes as follows. Leave the fk Office ID in tblJobs ... if a job is opened which does not have an equipment ID then whenever you open that job it gets the Office per the tblJobs fk Office ID. If you open a job and the equipment ID is known it uses the fk Office ID based off the fk Equipment ID. So basically as soon as fk Equipment ID is defined in tblJobs the fk OfficeID is no longer used.
Now in my mind both ways seem bad. I think mine is the best way and of course my friend thinks his is the best way. What would be the best way of the 2 and is there an option 3 that we overlooked?
Alright I will try and break it down further. We have several customers that will call out a job aka problem aka troubleticket anything not working like its suppose to. We give them a job number, so they can track the issue. The customer doesn't care which workcenter (office) will be working the job all they care about is having a number, so when they call us we can tell them the status. So tblJobs has a fkCustomerID so we know who called out the issue.
Now once we have that info we need to contact the correct workcenter so if it was a RADAR issue we notify the RADAR Maintenance workcenter if it is a radio issue we need to contact the Radio Maintainers. So tblJobs needs to know which workcenter is fixing the issue. So it would seem tblJobs would require fkOfficeID so we know which workcenter is fixing the job.
Once the workcenter is notified of the issue they will go see which piece of equipment is causing the issue. Wether it be a multiplexer, transmitter, reicever, voice switch etc and give us the ID for the equipment. Which would be stored in tblJobs as fkEquipmentID.
however the tblEquipment also has fkOfficeID. So one office is repsonsible for many pieces of equipment. And equipment can only belong to one workcenter. So I should be able to just get rid of fkOfficeID from tblJobs since fkEquipmentID already contains fkOfficeID ... the only issue with that if a customer opens the job we assign the customer to the job then notify the correct workcenter but we don't know the equipmentID so tblJobs wouldn't know what office it is assigned to till we have the equipmentID and this would cause some problems not knowing what workcenter was notified of the issue. We don't always get it correct because it might sound like a Radio problem but it might be a Telephone problem and 2 different workcenters work those things.
That is the best I can describe it ... I hope that helps it just seems like there is a better solution that I am just not seeing.
Oh I see what you are saying but that wouldn't be an issue. If you open an existing job it will get the officeID based on the equipmentID already stored in the tblJobs so you would only see equipment from that office.
For new jobs the officeID will default to whatever workcenter the users account is associated to so you would only see that equipment. So only one NA equipment would show up for that office.
So basically the cmbEquipmentID will always be filtered by an office so there wouldn't ever be multiplie NAs.