Information Technology Reference
In-Depth Information
Designing the IAM Database
It may seem a daunting task to design a custom database for your
organisation's
requirements,
but
hopefully,
the
following
tips
and
suggestions will make the job easier.
Tip 1
: Keep core tables minimal and store sets of related attributes in other
tables
Normally, the tendency is to store all attributes that have a one-to-one
relationship with an entity's primary key together in one table. We have
found that it is more flexible to group such attributes and store them in
separate tables.
For example, the user table should have very few attributes in it. The user's
name should be stored in a separate table with the related attributes of title,
first name, last name, preferred name, etc. Sometimes, it may be required to
store details of users for whom such information doesn't make sense, for
example system or admin accounts. Decoupling attributes in this way avoids
having to carry null values when they are meaningless.
Another example is the “application” table. Keep this minimal (just code and
description), and hold other attributes like URLs to be protected in another
table.
Tip 2
: Make the UUID a candidate key of the User table
Design the IAM database as you would any other application, i.e., the
primary key of the user table is a database-internal field, perhaps an
automatically-generated sequence number. This value will hence be the
foreign key in other tables that reference the user. The UUID needs to be a
candidate key in the User table, and that should be the logical entry point
from other systems. A simple join spanning the UUID and the User table's
primary key will allow you to access any user attribute, so this is a trivial
indirection. It's needlessly cumbersome to make the UUID the primary key.
Tip 3
: Consider application-specific roles rather than global roles
We haven't found much value in defining global (i.e., organisation-wide)
roles. What we think are relevant are global role names or role types. These
are standard identifiers such as “Administrator”, “User” and “Read-Only
User”. You can have codes and descriptions for each. Where these are useful
