Small business data management system
1. Conceptual Model
Entities
1. Users
2. Roles
3. Employee Profile
4. Department
5. Equipment
6. Training
Relationship
1. User -> Has -> Role (Many to Many relationship– to make it extendible)
2. User (Role Employee) -> works in -> Department (Many to One relationship)
3. User (Role Employee) -> needs -> Equipment (Many to Many relationship)
4. User (Role Employee) -> assigned to -> Training (Many to Many relationship)
5. Training -> conducted by -> User (Role Trainer) (Many to Many relationship)
6. User (Role Employee) -> has a -> Employee Profile (One to One relationship)
2. Logical Data Model
Entities With Attributes
1. Users
a. Id (Pk)
b. Name
2. Roles
a. Id (Pk)
b. Role Name
c. Description
3. Employee Profile
a. Id (Pk)
b. User Id (Fk)
c. Phone
d. Address
e. Hire date
f. Department Id (Fk)
4. Department
a. Id (Pk)
b. Name
5. Equipment
a. Id (Pk)
b. Name
6. Training
a. Id (Pk)
b. Name
Associative Entities (for many to many)
1. User Role
a. Id (PK)
b. User ID (FK)
c. Role ID (FK)
2. Employee Equipment
a. Id (PK)
b. User ID (FK)
c. Equipment ID (FK)
3. Training Trainer
a. Id (PK)
b. User ID (FK)
c. Training ID (FK)
4. Employee Training
a. Id (PK)
b. User ID (FK)
c. Training ID (FK)
Notes:
• user_id can be linked with either Employee or Trainer depending on their roles
in user_role.
• UNIQUE constraints on join tables ensure no duplicate relations.
3. Physical Data Model
1. users
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
2. roles
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
role_name VARCHAR(50) NOT NULL UNIQUE,
description TEXT
);
3. user_role (Associative: Many-to-Many between Users and Roles)
CREATE TABLE user_role (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
UNIQUE(user_id, role_id)
);
4. departments
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE
);
5. employee_profile
CREATE TABLE employee_profile (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
phone VARCHAR(20),
address VARCHAR(200),
hire_date DATE,
department_id INT REFERENCES departments(id)
);
6. equipment
CREATE TABLE equipment (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00
);
7. employee_equipment (Many-to-Many: Employee ↔ Equipment)
CREATE TABLE employee_equipment (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
equipment_id INT NOT NULL REFERENCES equipment(id),
UNIQUE(user_id, equipment_id)
);
8. training
CREATE TABLE training (
id SERIAL PRIMARY KEY,
name VARCHAR(150) NOT NULL
);
9. employee_training (Many-to-Many: Employee ↔ Training)
CREATE TABLE employee_training (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
training_id INT NOT NULL REFERENCES training(id),
UNIQUE(user_id, training_id)
);
10. training_trainer (Many-to-Many: Trainer ↔ Training)
CREATE TABLE training_trainer (
id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
training_id INT NOT NULL REFERENCES training(id),
UNIQUE(user_id, training_id)
);
