Skip to content →

People Profile database – a practice exercise

To support my learning goals of refreshing my business analyst skills and increasing my data literacy, I created an Entity Relationship Diagram (ERD), Database Schema, and Data Dictionary for a hypothetical people profile software application. The application’s purpose is to facilitate employees finding each other for collaboration opportunities or staffing decisions.

The envisioned data structure has five entities:

  • Employees
  • Communities of Practice (CoP)
  • Topics
  • Employee Roles
  • Company Functions

The core relationships, working counterclockwise through the ERD are:

  • Community membership
  • Employee expertise / interests
  • Employee connections to other employees, i.e. an Enterprise Social Network
  • Employee role
  • Employee function

All relationships are many-to-many cardinality except for Employee-to-Function where an employee can only work in one company Function at a time.

For this database design work, I targeted “illustrative” contrast to “exhaustive”. For example, in the real-world the Employee table would have many more attributes that describe an employee.

I used the freeware ERDPlus to create the ERD and schema, which I was introduced to in a data analytics Coursera course. If not for this familiarity, I might have considered using Lucidchart that looks similar and perhaps is a bit more powerful than ERDPlus.

To see my full work:

Download ERD as Image or ERDplus source file

Download Database Schema as Image or ERDplus source file

Download Data Dictionary spreadsheet (xls)

As a database newbie, I have some doubts regarding my work and I would especially appreciate guidance from others on the following:

  1. Is it best to have CoP_Membership and Community_Leader as separate tables (as now shown) or is better to have only a CoP_Membership table and add an additional attribute for Membership_Type with values of ‘member’ and ‘leader’? A user story that would leverage the implementation: “As a manager of the Community of Practice initiative, I need to be able to send an email to all community leaders so that I can maximize value of the community initiative by sharing community leading practices, recognition for volunteers, announce of infrastructure improvements, etc. with the community leaders.”
  2. Is the Employee_Connections shown correctly on the schema, or should this be shown as a recursive relationship?
  3. Is the bridge table naming leading practice? I have seen other people use names that more strongly follows the ERD language for the relationship. For example ‘Has_Interests’, contrast to my ‘Employee_Interests’

Next Steps:

  1. Gain answers to my schema questions
  2. (perhaps) build the database in MySQL and populate with dummy data to then use with data visualization
  3. Research what people profile solutions are available from enterprise software companies — to support my knowledge management refresh learning objective, in contrast to using for reverse engineering of the database schema
  4. Craft other hypothetical project deliverables for further practice, e.g. personas, user stories, wireframes, etc.

Published in Business Analysis Portfolio

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *