Next I will present an example of a simple database model for Agile methodology. Let’s consider a project management tool for a company that uses Agile methodology to manage projects.  First we need to know how Agile methodology works. Then we design a conceptual model and present this model to the programming team for evaluation.

Agile Methodology Example

Agile Rules:

In an agile company one team works for a single project. A project consist of several epics and each epic is represented by stories. A developer can work in a single team and can be assigned to one or multiple stories.

Draft Model:

Here is the proposed draft model:

This model shows 3 entity tables (orange) and 2 associations (green). Between the entities we draw references. For a better understanding of the model references can have a name.

Other Rules:

  • An epic has a business value from 0 to 10;
  • A story has a Fibonacci number: 1,2,3,5,8,13;
  • A story has to be resolved into one sprint;

Entity tables:

An entity table describe a physical object or well-known domain concept. Usually a table has several attributes and a column that contains one unique number for each record. This number may start from 1 to the number of rows in the table. It can have a special name like: ID (Identifier). This column is also called “primary key”.

  • DEVELOPER:
    • Primary key: ID
  • PROJECT:
    • Primary key: ID,
    • Foreign key: OWNER_ID
  • EPIC:
    • Primary key: ID,
    • Foreign keys: (PRO_ID, TEAM_ID)

Associations:

An association is the generic terminology used for a table that has purpose to connect two entity tables. The primary key of an association can be an ID or a composite key from two foreign keys. These foreign keys are two columns that have values from the entity table.

  • TEAM:
    • Primary key: ID,
    • Foreign keys: (DEV_ID, PRO_ID)
  • STORY:
    • Primary key: ID,
    • Foreign keys: (DEV_ID, EPIC_ID)

References:

  1. One project has one project owner: Relation 1:1
  2. One project has at least one epic or multiple epics: Relation 1:M
  3. One epic has at least one or multiple stories: Relation 1:M
  4. One team has at least one member or many members: Relation 1:M
  5. One team is assigned to a single project: Relation 1:1
  6. One developer can be assigned to one or several stories: Relation 0:M
  7. One story can be unassigned: do not have a developer.

These references are actually implementing several fundamental Agile rules.

ER Diagram

ER diagram is used to show attributes, data types, primary keys and references into a single model diagram. This is also called physical model.  This model is used by developers to create a database structure using SQL.

Note: If is possible in a diagram do not cross references one over the other. This will clutter the diagram and make it difficult to read.

Conclusion:

This example demonstrates how a problem can be implemented into a relational database using tables and references. Now we know how to create entity tables and associations.