Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Issue with DB table design

    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?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's pretty hard to know what to do without the real facts behind the operation. Just how does office apply to jobs?

    Seems to me that a job might have multiple pieces of equipment... and each job is done for a single office... but I'm guessing.

    Spell out how the operation goes and we might be able to give you better advice.

    in my mind both ways seem bad
    Same. I'd be looking for option 3.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2008
    Posts
    4
    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.

    And this seems fine ...

    tblJobs
    -fkOfficeID
    -fkCustomerID
    -fkEquipmentID
    -JobDescription

    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.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok. Thanks

    Your friend has the better approach for user friendliness. You have the better approach for table design simplicity.

    I'd go with your friend's method. User friendliness is not sacrificial in my book
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2008
    Posts
    4
    Thanks StarTrekker for the reply but I am not sure what you mean by "User Friendliness" both ways would be transparent to the user.

    I could still add a combo box on the form that has all the workcenters and when you select one it filters the equipment combo box.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Some users won't cope that easily with 10 different selectable "unknown" records.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Oct 2008
    Posts
    4
    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.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The either is fine.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •