locked
Help for equipment tracking database RRS feed

  • Question

  • Hi all,

    i have to create a database to track IT equipment assignment within my organization.

    I sketched the ER diagram below.

    Some of my requirements are:

    1) keep track of which devices are assigned to each employee

    2) store data about where those devices are placed

    3) some employees works for different department and therefore uses different devices (in different lcoations)

    4) some device are shared (Printers, Scanning station,...) so are not assigned to a specific employee

    5) keep track of equipment movements (including reception, first functional test, maintenance,...) and relative date.

    My first question is : do you agree  on the use of ternary relationship among Employee, Equipment and Location ? the fact that some device are not assigned to employee may affect this decision?

    The second question is : do you think the design could work or need some adjustments?

    Thanks.

    Friday, February 15, 2013 2:16 PM

Answers

  • I personally would go for the seperate subtype tables. The beauty of the subtype is that it allows you to have the structure of the relational database to define the simple Equipment object that lets you naturally select a lit of all equipment, as well as lists of specificly only Computers, along with their information in a simple manner. I might also implement maintence at the subtype to allow for specific requirements (like printer cleanings) to be documented more specifically.

    A subtype for computer would also allow you to easily have a table for ComputerPerephials so you could define > 2 hard drives, or sticks of ram etc. You might even want to link equipment together in a bundle using a self reference to allow the monitor(s) and printers(s) to be bundled together as a package...(may not of course :).

    Not sure I understand the movements table, is it really just a notes table to allow people to note what has been done to the equipment? Would you fill it in before or after the action has taken place? I like David's Event or even Notes in that case..

    I would definitely question the ternary relationship. I would probably have gone with independent relationships, implementing as EquipmentLocationAssignment and EquipmentEmployeeAssignment, to allow assignment for location and employee independently, but also to allow for assignment to multiple people, or multiple locatons.  A printer might be a very simple example, having two people share the same printer...


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Saturday, February 16, 2013 3:56 AM
    • Marked as answer by Kalman Toth Tuesday, February 26, 2013 3:48 PM
    Saturday, February 16, 2013 3:08 AM

All replies

  • Based on your description, that looks reasonable.  "Movements" is probably unnecessarily specific, you can probably get away with a generic EquipmentEvents table, which has a more complete history of each item, including assignment changes. 

    Practice varies, I would not use separate subtype tables for the category here, and would probably add an XML column on Equipment for "Specifications" to hold Equipment details you haven't promoted to individual columns and you would only want to view on a single item.

    And Assignment  is the only linking table you will need, and you can even fold that into Equipment if you are only tracking the current AssignedToEmployee and AssignedToLocation (not historical assignments, assignment date, scheduled assignment end date, etc).

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, February 15, 2013 6:15 PM
  • I personally would go for the seperate subtype tables. The beauty of the subtype is that it allows you to have the structure of the relational database to define the simple Equipment object that lets you naturally select a lit of all equipment, as well as lists of specificly only Computers, along with their information in a simple manner. I might also implement maintence at the subtype to allow for specific requirements (like printer cleanings) to be documented more specifically.

    A subtype for computer would also allow you to easily have a table for ComputerPerephials so you could define > 2 hard drives, or sticks of ram etc. You might even want to link equipment together in a bundle using a self reference to allow the monitor(s) and printers(s) to be bundled together as a package...(may not of course :).

    Not sure I understand the movements table, is it really just a notes table to allow people to note what has been done to the equipment? Would you fill it in before or after the action has taken place? I like David's Event or even Notes in that case..

    I would definitely question the ternary relationship. I would probably have gone with independent relationships, implementing as EquipmentLocationAssignment and EquipmentEmployeeAssignment, to allow assignment for location and employee independently, but also to allow for assignment to multiple people, or multiple locatons.  A printer might be a very simple example, having two people share the same printer...


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Saturday, February 16, 2013 3:56 AM
    • Marked as answer by Kalman Toth Tuesday, February 26, 2013 3:48 PM
    Saturday, February 16, 2013 3:08 AM
  • After more requirements analysis, i ended up drawing this ER diagram.

    The manager wants to know only the current equipment assignment but want to know all history of the maintenance operations on each device; maintenance could be done by internal technician or external service company.

    Tuesday, February 19, 2013 11:48 AM
  • Having the network config off of IT Equipment means you ca have a monitor with an ip address. Might be better to have a network config table that is then a parent to the objects that can have a network config

    NetworkConfig
    -----------------
    NetworkConfigId PK
    IP Address, etc

    PC
    -------------------
    <other columns>
    NetworkConfigId [FK to NetworkConfig]

    Printer
    -------------------
    <other columns>
    NetworkConfigId [FK to NetworkConfig]


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, February 19, 2013 3:19 PM
  • I used the 0 cardinality Equipment-has-NetworkConfig to manage device with no network connections. But I understand what you mean, and actually it's a better solution. Thanks
    Tuesday, February 19, 2013 3:26 PM