You have actually hit upon a fairly advanced concept of relational database
design here, that of normalization to Fifth Normal Form (5NF). I won't go
into the formal definition of this right now as it’s a little abstract. I
can better explain it by an example.
You'll start with three tables, Employees, Computers and Software, each of
which represents one of the three main entity types with which you are
currently concerned. Lets assume the primary keys are EmployeeID, ComputerID
and SoftwareID. The Computers table would include the AssetNumber column.
Now, you can clearly model the relationship between the above tables by a
table with foreign key columns EmployeeID, ComputerID and SoftwareID
referencing the primary keys of the three 'referenced' tables. However, this
table could give rise to certain update problems, though this is not
necessarily the case as it depends on the real world constraints governing
the database. These problems relate to the insertion or deletion of rows
from the table, and are concerned with the necessity to insert/delete one row
if another is inserted/deleted in some circumstances, but not necessarily the
converse.
You might be familiar with normalizing a table by decomposing it into two
tables, and you could decompose this table into two tables, one with
EmployeeID and ComputerID columns, the other with ComputerID and SoftwareID
columns. The problem with this is that if you join the two tables over
ComputerID you may well end up with spurious rows in the result table
implying that some users use certain software on certain computers when this
is not in fact the case. So you need to include a further table with columns
EmployeeID and SoftwareID. When this is joined to the join of the first two
tables, this time over EmployeeID and SotwareID it removes the spurious rows.
Now that probably sounds like complete gobbledegook, but don't worry about
that. The reason I've spelt it out is that you'll see that the three tables
Employees_Computers, Computers_Software and Employees_Software pretty well
exactly match your description of the requirements "which users use which
computer, which computers have which software, and which users use which
software". So intuitively you've already identified the three tables
required to model the relationships between the three main tables, and gone
most of the way towards answering your own question.
Having set up the tables as described above you can return which employees
use which software on which computers with a query such as:
SELECT FirstName, LastName, ComputerName, SoftwareTitle
FROM Software
INNER JOIN (Computers
INNER JOIN (Employees
INNER JOIN ((Employees_Computers
INNER JOIN Computers_Software
ON Employees_Computers.ComputerID = Computers_Software.ComputerID)
INNER JOIN Employees_Software
ON (Computers_Software.SoftwareID = Employees_Software.SoftwareID)
AND (Employees_Computers.EmployeeID = Employees_Software.EmployeeID))
ON (Employees.EmployeeID = Employees_Software.EmployeeID)
AND (Employees.EmployeeID = Employees_Computers.EmployeeID)
AND (Employees.EmployeeID = Employees_Computers.EmployeeID))
ON (Computers.ComputerID = Employees_Computers.ComputerID)
AND (Computers.ComputerID = Computers_Software.ComputerID))
ON (Software.SoftwareID = Employees_Software.SoftwareID)
AND (Software.SoftwareID = Computers_Software.SoftwareID);
A report can be based on the query. The report wizard will do most of the
work in setting this up.
As you already have an Employees table in this model you can then use this as
the basis for HR management, leave bookings etc by creating other tables as
necessary, which can be related to Employees on the EmployeeID column.
Ken Sheridan
Stafford, England
I am looking for the best starting point to create a database to track IT
software and Hardware
In my ideal world it would print off reports stating which users use which
computer, Which computers have which software, and which users use which
software and probably a mix of all 3. would probably also be helpful if I
could include asset numbers for all our IT hardware.
Currently all this info is in rather unmanageable spreadsheets. I have a
working (and rapidly expanding) knowledge of Access and am quite happy to
read reference materials and follow suggestions.
Any advice/ help/ guidance would be greatly appreciated.
We would also be looking at another one to track HR stats and leave bookings
to simplify reporting processes.
Thanks in advance
--
