Entity Sets

• Company(company_id, company_name, company_street, company_city, company_state, company_zip, company_vat, company_email, created_at, updated_at)

• User(user_id, user_name, user_email, password, user_role, company_id, created_at, updated_at)

*Reference: company_id → Company(company_id)

• Employee(employee_id, employee_name, employee_email, employee_role, employee_status, employee_street, employee_city, employee_state, employee_zip, employee_iban, employee_bank_name, employee_swift, employee_cv_ref, employee_photo_ref, company_id, created_at, updated_at)

*Reference: company_id → Company(company_id)

• Candidate(candidate_id, candidate_name, candidate_email, candidate_status, candidate_cv_ref, candidate_stage, candidate_notes, company_id, created_at, updated_at)

*Reference: company_id → Company(company_id)

• Client(client_id, client_name, client_representative, client_email, client_street, client_city, client_state, client_zip, client_vat, client_logo_url, company_id, created_at, updated_at)

*Reference: company_id → Company(company_id)

• Invoice(invoice_id, invoice_no, invoice_issue_date, invoice_due_date, invoice_notes, invoice_status, company_id, client_id, created_at, updated_at)

*References:

company_id → Company(company_id) client_id → Client(client_id)

• LineItem(item_id, description, quantity, unit_price, currency, total, invoice_id)

Reference: invoice_id → Invoice(invoice_id)

• EmailTemplate(template_id, type, subject, body, company_id, created_at, updated_at)

*Reference: company_id → Company(company_id)

Relationship Sets Between Entities

• Employee_Belongs_To_Company(employee_id, company_id)

References:

employee_id → Employee(employee_id) company_id → Company(company_id)

• Candidate_Belongs_To_Company(candidate_id, company_id)

References:

candidate_id → Candidate(candidate_id) company_id → Company(company_id)

• Client_Belongs_To_Company(client_id, company_id)

References:

client_id → Client(client_id) company_id → Company(company_id)

• Invoice_Belongs_To_Client(invoice_id, client_id)

References:

invoice_id → Invoice(invoice_id) client_id → Client(client_id)

• Invoice_Belongs_To_Company(invoice_id, company_id)

References:

invoice_id → Invoice(invoice_id) company_id → Company(company_id)

• LineItem_Belongs_To_Invoice(line_item_id, invoice_id)

References:

item_id → LineItem(item_id) invoice_id → Invoice(invoice_id)

• User_Belongs_To_Company(user_id, company_id)

References:

user_id → User(user_id) company_id → Company(company_id)

• EmailTemplate_Belongs_To_Company(template_id, company_id)

References:

template_id → EmailTemplate(template_id) company_id → Company(company_id)

DDL скрипта за бришење и креирање на табелите

https://develop.finki.ukim.mk/projects/agency-os/attachment/wiki/DDL/dodavanje.sql

DML скрипта за пополнување на табелите со податоци

https://develop.finki.ukim.mk/projects/agency-os/attachment/wiki/DDL/kreiranje.sql

Relational Diagram