Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    1

    Question Unanswered: Table design question in Asset Tracking database

    I just started a job where we have a very rudimentary Access (v. 2002) database for tracking our computers, hardware, software, users, etc. I want/need to improve it (or rewrite it) as it will make my job much less annoying
    Here are the [somewhat simplified] tables as they exist:

    Computers
    ComputerID (PK, Autonumber)
    Mfgr
    Model
    SerialNum
    Processor
    Memory
    StationID (FK to Stations.StationID)

    Hardware
    HardwareID (PK, Autonumber)
    Description
    Mfgr
    Model
    SerialNum
    StationID (FK to Stations.StationID)

    Software
    SoftwareID (PK, Autonumber)
    Publisher
    Title
    SerialNum
    Seats

    SoftwareStations (Junction Table)
    SoftwareID (FK to Software.SoftwareID)
    StationID (FK to Station.StationID)

    Stations
    StationID (PK, Autonumber)
    StationName (the name of the computer on the network)
    UserID (FK to Users.UserID)

    Users
    UserID (PK, Autonumber)
    UidNum (user number on our linux server, e.g. 1006)
    Login (mail/network login ID)
    LastName
    FirstName
    IsActive (Boolean)

    Currently, there are NO relationships defined, which means when we upgrade a user's station, I have to go through all the tables and update the info by hand, which is just silly.

    Now, for the question. First of all, the Computers table seems to have been deprecated in favor of putting all new computers in the Hardware table instead, which made me start wondering about the best way to design and name the tables in the first place. The guy who originally created this database apparently stopped using the Computers table because he figured computers fall under the category of hardware, and thus lumped them both under the same table. I don't know if I agree, and may need to update/cut/paste a bunch of data to re-impliment the Computers table.
    Anyway, I'm thinking of having four base tables of "Users", "Computers", "Software", and "Hardware". A fifth table named Stations would contain one Autonumbered PK, and four Foreign Keys, one to each of the four base tables I just mentioned. Am I crazy? For some reason this sounds like a bad design, but I can't decide why. My logic is that Users, Computers, Software, and Hardware are pretty distinct categories, and each deserves its own table. A Station, however, consists of one computer (I thought about allowing multiple, but settled on just one single computer per station), one or more pieces of software, one or more pieces of additional hardware (at least one is guarenteed, as the monitors are in the hardware table, e.g.), and one or more users (multiple logins are possible on each workstation). So, the Software, Hardware, and Users kinda fit the Many to Many relationship, right? In other words, there are many stations, each of which contains many users, many pieces of hardware, and many pieces of software. So I guess the ultimate question is, where does the SoftwareStations junction table come into play (see table list above)? Do I need multiple junction tables, e.g. in addition to the existing SoftwareStations junction table, also have a UserStations and a HardwareStations junction table? Or can you make a junction table with multiple many-to-many junctions like I seem to be doing here (and am probably doing incorrectly)?

    Smack me if I'm being criminally silly. It's been two years since I took my New Horizons Access courses, and some of these concepts have just flown from my brain...

    Also, I apologize in advance if my problem isn't at all clear. Please let me know, and I will attempt to explain better.

    Thanks for any help! I have other questions, but I'm going to take this one at a time....
    -Scott Brush

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    1st blush ...

    User Table ...
    Computer Table ...
    ComputerUser Table ...
    Component Table ...
    ComputerComponent Table ....
    Software Table ...
    ComputerSoftware Table ...
    Network Table ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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