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:
- Communities of Practice (CoP)
- 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 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:
- 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.”
- Is the Employee_Connections shown correctly on the schema, or should this be shown as a recursive relationship?
- 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’
- Gain answers to my schema questions
- (perhaps) build the database in MySQL and populate with dummy data to then use with data visualization
- Research what people profile solutions are available from enterprise software companies — to support my knowledge management refresh learning objective, in
contrastto using for reverse engineering of the database schema
- Craft other hypothetical project deliverables for further practice, e.g. personas, user stories, wireframes, etc.