Creating your database
As described before, a role-based access control system decides who can access an entity according to the access roles. To do so, your database must store the information about users and the available roles. Since we want to deal with fingerprint recognition, your database must be able to store this information either. So, your database must be set to store the following information:
In this example we’ll create the database schema using Microsoft Access 2003, leaving to the user the implementation using another database solution.
The proposed database schema is shown below:
In this schema we have a table to store all the user information (Users), a table to store the fingerprint information (Fingerprints) and a table to store the systems’ information we want to control the access to (Systems).
NOTE: The information stored on Fingerprints table is based on Fingerprint SDK documentation.
The relationships are shown as blue lines and can be explained as follows:
- Each user can register several fingerprints on the system, but each registered fingerprint belongs to a single user (one-to-many);
- Each user can access several systems and each system allows access to several users (many-to-many);
This schema is showing only the main fields of all tables. The relationship between the tables is ensured by Primary keys (PK) and Foreign keys (FK). Also, to create the many-to-many relationships we need a Junction Table. Based on this, a complete schema would look like:
In this schema, the Primary Keys are shown in soft green and the Foreign Keys are shown in soft yellow. The primary keys are the unique representation of a single data on your table and the foreign keys are the representation of a primary key that exists on another table. Notice the junction table UserSystem, which maintains the foreign keys for Users and Systems tables.
To create your database, proceed as follows:
- Open MS Access.
- Click File → New… → Blank database.
- Save database as “AccessControl.mdb”.
You'll now be presented to the following screen:
Your database is ready to be used!
To create the tables, using your newly created database:
- Double click Create table in Design view. The following screen will appear:
- Enter the values below (Description field is optional):
|Field name||Data Type||Additional steps|
|Name||Text||Set Field Size to 50|
|Office||Text||Set Field Size to 20|
|Position||Text||Set Field Size to 20|
- Save this table as Users and, when asked, set UID as primary key.
- You'll be presented to the following screen (notice the key in front of UID field):
- Using our proposed schema, repeat these steps for tables Fingerprints, Systems and UserSystem.
- Your database is now complete! Your complete access database will lok like this:
- Click Tools → Relationships.
- Click Relationships → Show table... . You'll be presented to the following screen:
- In Show Table box, select all the tables and click Add – all tables will show up in your Relationships window (HINT: hold the Ctrl key while clicking on each table).
- On each table, drag the primary key over the other table’s corresponding foreign key.
- For each relationship shown on Edit relationships box, ensure that each primary key is related to the correct foreign key on related table and that the relationship type is One-To-Many (the Enforce Referential Integrity field is optional but recommended here):
- Once you have finished, your Relationships window will look like follows (compare it to our schema):
Your database is ready to be used. Ensure you know the location of your database file AccessControl.mdb because you’ll need to know where it is to proceed.