The Logical Data Model of the EMPLOYEE AND ROLE Subject Area includes the following entities:
This entity records information about each Employee of an Online Food Ordering Store.
Attributes | |
---|---|
ID | PRIMARY KEY |
Store_ID | Lookup for the Store where the Employee is registered. NOT NULL |
First_Name | Employee first name NOT NULL |
Last_Name | Employee last name NOT NULL |
Phone | Employee phone number. UNIQUE identifies the Employee. Cannot register two employees with the same phone number NOT NULL UNIQUE |
Employee email. UNIQUE identifies the Employee. Cannot register two employees with the same email. NOT NULL UNIQUE | |
Username | Employee username for application. UNIQUE identifies the Employee. Cannot register two employees with the same username. NOT NULL UNIQUE |
Password | Employee password for application. NOT NULL |
Created_Date | Date and Time (timestamp) when the Employee record was created. NOT NULL |
Last_Login_Date | Date and Time (timestamp) of the Employee last login. |
Is_Active | Flag attribute to indicate if the Employee still has access to application. NOT NULL |
Comments | Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees |
Example
Is_Active flag is set to TRUE when the Employee record is created and the Employee obtain access to application.
Is_Active flag becomes FALSE and the Employee access to application is revoked when Employee leaves the company.
List of role types (roles) that can be assigned to an Employee. Roles help to control what application functionalities can be accessed by an Employee.
Attributes | |
---|---|
ID | PRIMARY KEY |
Name | Role Type name NOT NULL UNIQUE |
Description | Role Type description |
Example
ROLE_TYPE
Name |
---|
Administrator |
Manager |
Staff |
If you don't need to control application functionalities by role, add 1 record: |
All roles |
List of function types (application functionalities) that can be assigned to a role type.
Attributes | |
---|---|
ID | PRIMARY KEY |
Name | Function Type name NOT NULL UNIQUE |
Description | Function Type description |
Example
FUNCTION_TYPE
Name |
---|
Edit Store |
Edit Menu |
Edit Roles |
View Invoices |
etc. |
If you don't need to control application functionalities by role, add 1 record: |
All functionalities |
This entity records the application functionalities assigned to each role.
Attributes | |
---|---|
ID | PRIMARY KEY |
Role_Type_ID | Lookup for the Role. NOT NULL |
Function_Type_ID | Lookup for the application functionality to be assigned to the Role. NOT NULL |
Comments | Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees |
CONSTRAINT UK_Role_Function UNIQUE (Role_Type_ID, Function_Type_ID).
Cannot assign two times the same application functionality to a role.
Example
ROLE_FUNCTION table contains the Role_Type_ID and the Function_Type_ID for the allowed Function_Type to Role_Type assignments:
Role Type ID | Name | Function Type ID | Name |
---|---|---|---|
2 | Administrator | 23 | Edit Store |
2 | Administrator | 27 | Edit Roles |
2 | Administrator | 7 | View Invoices |
3 | Manager | 28 | Edit Menu |
3 | Manager | 7 | View Invoices |
4 | Staff | 34 | Enable Is_Ordering_Pause |
If you don't need to control application functionalities by role, add 1 record with the Role_Type_ID and the Function_Type_ID for "All roles", "All functions" : | |||
1 | All roles | 1 | All functions |
Important The above values for the Role_Type_ID and the Function_Type_ID are just samples. When populate the ROLE_FUNCTION table with values, you should use the Role_Type_ID and the Function_Type_ID values from your database.
This entity records the Employees assigned to each Role.
Attributes | |
---|---|
ID | PRIMARY KEY |
Role_Type_ID | Lookup for the Role. NOT NULL |
Employee_ID | Lookup for the Employee to be assigned to the Role. NOT NULL |
Assigned_Date | Date and Time (timestamp) when the Employee was assigned to the Role. NOT NULL |
Revoked_Date | Date and Time (timestamp) when the Role was revoked from Employee. If Revoked_Date is NULL then the Role is still assigned to Employee. |
Comments | Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees |
This entity records information about the application login sessions of each Employee, since the Employee was created and was granted access to application, to the time the Employee access to application was revoked.
Attributes | |
---|---|
ID | PRIMARY KEY |
Employee_ID | Lookup for the Employee who initiated application login. NOT NULL |
Employee_Role_ID | Lookup for the Role that was selected by Employee to login to application. Employee can have many Roles assigned but need to select one role to login to application. NOT NULL |
Login_Time | Date and Time (timestamp) when the Employee initiated the login to application. NOT NULL |
Logout_Time | Date and Time (timestamp) of the Employee logout from application. If Logout_Time is NULL then Employee is still logged to application. |
Device_IP | The IP of the device from which the Employee initiated login to application. NOT NULL |
Comments | Internal notes used for clarifications on record content. Can be seen and edited only by Store Employees |