Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Fire Department Personnel Accountability Database design trouble

    I am new to database design and I apologize for my ignorance.

    I am working on a database that can record the location of personnel and equipment for my fire department, as we are currently doing it with paper.

    I was thinking of using the following tables:
    1. Employee
    2. Apparatus (Fire engines, Trucks, Utility vehicles, etc.)
    3. Locations (Fire stations, Major Incident Locations, etc.)

    The problem I am having is that an employee can be in a piece of equipment (Apparatus) OR in a Location. However, equipment must be at a station or Incident (Location). Therefore, the employee can be in the Apparatus which is in a Location OR in a Location without any apparatus.

    I do not know how to model or implement this. Maybe I am approaching this all wrong.

    Any help would be greatly appreciated.

    Thanks,
    Nick

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Looks like you actually have three separate relationships:

    Employee <---> Location
    Apparatus <---> Location
    Employee <---> Apparatus (optional)
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    Thank you for your reply.

    I guess what I'm trying to figure out is how to show an employee in the location OR the apparatus. Then consequently show the apparatus in the station.

    How should this be structured? Would it be useful to create a class or a self relationship to show the hierarchy of the locations vs. engines?

    Thank you very much for your help. It is invaluable.

    Nick

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by nicolassal View Post
    show an employee in the location OR the apparatus.
    I guess I don't understand why this is "OR". Can't that be "AND"? Both employees and apparatus are always at some location, and there is an additional relationship when certain employee arrives to a location by certain apparatus, but that's not necessarily the case.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    i'd looka t soemthign such as:-
    persons
    stations
    ..an intersection table that identifies what persons are on shift at what station
    equipment
    ..an intersection table that identifies what persons are on shift at what station and allocated to a piece of equipment

    ..mind you I'd probably do it on paper as opposed to trying to create a db to handle this
    ...failing that a rack with sliders or whiteboard. move the name to on/off shift, move the name to an appliance
    ..it seems overkill to create a db in Acces to handle this. I coudl understand it if say it was part of the overall fire control system but not on an individual fire station
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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