For someone as new to this sort of thing as you say you are you've done well
in terms of you analysis of the relationship types. You've not got the
relationships quite right though, and you are underestimating how many tables
are needed so I'll start from square one.
Firstly you should start with three tables which represent the three entity
types Servers, Accounts and Users, the first having one row per server, the
second one row per account and the third one row per user. These will
contain all the attributes of each entity type, ServerName, AccountName,
FirstName, Lastname etc. Also each will have a primary key column, which
I'll call S#, A# And U# for brevity, though something like UserID etc would
be better in reality.
What you need to do now is model the relationship types between these three
entities. Now it might be thought that the equivalent of your second table
on its own would do that, a table having columns S#, A# And U# as foreign
keys referencing the primary keys of the three 'referenced' tables. But lets
look at this with some data:
SAU
S# A# U#
1 1 2
1 2 1
2 3 1
2 4 1
2 4 2
Now this tells us all we need to know, that server 1 hold accounts 1 and 2,
server 2 holds accounts 3 and 4 and user 1 has access to accounts 2,3 and 4
and user 2 has access to accounts 1 and 4. So we don't need another table?
Wrong!
Lets assume that user 2 falls under a bus on his way to work one day, so his
employment is terminated with extreme prejudice. OK, we delete user 2 from
the users table and the first and last rows from the SAU table above. But
hold on, what's happened to account 1? We no longer know where it is. So we
have to decompose SAU into two tables:
SA
S# A#
1 1
1 2
2 3
2 4
AU
A# U#
1 2
2 1
3 1
4 1
4 2
If we now join these tables on A# the result is the original SAU, so the
decomposition has been 'non-loss', which is what it should be. We can now
delete the first and last rows from AU, but we still know that account 1 is
on server 1 from the first row in SA. We can now all have a morning away
from the office at user 1's wake, drinking lots of Guinness without having to
worry about where account1 is.
So, you were heading in the right direction! The only real mistake you made
as regards the relationship types was redundantly including the server in
your first table; and you didn't identify the need for the three main
referenced tables.
This process can in some circumstances be extended beyond this. In reality
it wouldn't apply in this scenario as it would mean having the same account
on more than one server, but if we unrealistically assume that to be the case
then joining the two tables could produce a result table which includes
spurious rows not present in the original SAU table. In that situation it
would be necessary to decompose it into three tables, adding a SU table to
the SA and AU tables. This would be normalization to Fifth Normal Form
(5NF), also known as Projection-Join Normal Form (PJ/NF), which is quite a
difficult concept to get your teeth around as its defined in terms of 'join
dependencies' as well as the usual 'functional dependencies' which define the
lower normal forms.
Ken Sheridan
Stafford, England
