Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012

    Unanswered: Basic Relationship Advice (One to One?)


    I work in an IT department and need to create a database which holds staff member details (passwords etc), plus also the details of their PC.

    I have created two tables ("Staff" and "Computers").

    The Staff table has Staff Name, Office, Contact Details and will have fields for all of their various passwords (Windows/Skype/Dropbox/Emails etc).

    The Computers table has all of the relevant information about the computer (Computer Name, Make, Model, Serial Number, Office Package Installed, Security Tag etc).

    I need these to be seperate tables because we have a high turnover of staff, so these computers will be reused for different staff.

    However, I seem to have nothing but questions on how this would work.

    How do I set up the relationship between these two tables/forms so that each member of staff can be allocated a computer?

    And also, can I create a field in my form (or subform) so I can choose the computer name in a drop down field for that member of staff, and it will auto fill the rest of the information?

    How does it avoid duplication, i.e, if I try to allocate a computer to a member of staff, but it is already selected for another member, what happens?

    Are there any other potential issues you can see with this set up?

    Thank you in advance for your assistance.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    there's various issues here
    first off I'd check with your network trolls about storing passwords. they are feck all use if your computer becomes compromised. what your trolls should have is a piece of software that can remotely set/reset passwords.

    one table for staff
    one table for computers
    except that users may have multiple software packages, which suggest there should be a table for software, and a table (a junction or intersection table) that identifies what software is installed on what computers. but then again a SNMP discovery should be able to tell you that

    modelling a user and a computer can be tricky
    you could store the users current computer in users table, or vice versa store the computers current user in the computer table. both these approaches work, however they also mean you dot' have a history (so you don't know who had computer x when)
    if you need the history then consider another junction/intersection table that identifies what user had what computer for what period

    enforcng your only one computer in use rule is straightforward if:-
    you store the current user in the computer table (defacto there's only on user per computer), however you'd have to enforce a constraint that limited the user to one computer.... do that with a unique index on the user in the computers table

    for the converse if you store the computer in the users table then you only can ever have one computer per user but you will have to declare a unique index on the computer column to force only one registration of a computer within tthe user tabel

    its a bit more complex with the junction table, Im not sure you can enforce that with constraints within an Access db, you might have to do that as part of the applicationm software
    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