Entity: ADMIN_USER

Top

This entity records information about the Administrator of the Car Service application database.

  • Each Car Service application database has only one active Administrator.
  • The Administrator has the reponsibility to populate the tables that contain List of Values: VEHICLE_CATEGORY, VEHICLE_FUEL_TYPE, VEHICLE_MODEL, etc which are general and can be used by all Shops.
  • The Administrator has also the responsibility to create new Shops and to add a Shop Administrator for each new Shop.
Attributes
ID PRIMARY KEY
First_Name Administrator first name NOT NULL
Last_Name Administrator last name NOT NULL
Phone Administrator phone number. UNIQUE identifies the Administrator. Cannot register two Administrators with the same phone number NOT NULL UNIQUE
Email Administrator email. UNIQUE identifies the Administrator. Cannot register two Administrators with the same email. NOT NULL UNIQUE
Username Username for application. UNIQUE identifies the Administrator. Cannot register two Administrators with the same username. NOT NULL UNIQUE
Password Administrator password for application. NOT NULL
Created_Date Date and Time (timestamp) when the Administrator record was created. NOT NULL
Last_Login_Date Date and Time (timestamp) of the Administrator last login.
Is_Active Flag attribute to indicate if the Administrator still has access to application. NOT NULL
Comments Internal notes used for clarifications on record content.

Example

Is_Active flag is set to TRUE when the Administrator record is created and the Administrator obtains access to application database.

Is_Active flag becomes FALSE and the Administrator access to application is revoked when for example Administrator leaves the company.

Entity: EMPLOYEE

Top

This entity records information about each Employee of a car service Shop.

Attributes
ID PRIMARY KEY
Shop_ID Lookup for the Shop 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
Email 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 Shop Employees

Example

Is_Active flag is set to TRUE when the Employee record is created and the Employee obtains access to application.

Is_Active flag becomes FALSE and the Employee access to application is revoked when Employee leaves the company.

Entity: ROLE_TYPE

Top

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
Shop Admin
Shop Manager
Shop Technician
If you don't need to control application functionalities by role, add 1 record:
All roles

Entity: FUNCTION_TYPE

Top

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
Add Employee
Edit Shop Details
Create Order
Edit Order
Approve Order
View Order
View Invoices
etc.
If you don't need to control application functionalities by role, add 1 record:
All functionalities

Entity: ROLE_FUNCTION

Top

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 Shop 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 Shop Admin 23 Add Employee
2 Shop Admin 27 Edit Shop Details
3 Shop Manager 7 Approve Order
4 Shop Technician 30 Create Order
4 Shop Technician 34 Submit Order
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.

Entity: EMPLOYEE_ROLE

Top

This entity records the Employees assigned to each Role.

Attributes
ID PRIMARY KEY
Shop_ID Lookup for the Shop where the Employee was registered. NOT NULL
Employee_ID Lookup for the Employee to be assigned to the Role. NOT NULL
Role_Type_ID Lookup for the Role. NOT NULL
Workplace_ID The main Workplace within the Shop where the Employee works. 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.

Entity: EMPLOYEE_LOGIN

Top

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.
Top