We start the conceptual data modeling by going through the user stories and finding the entities and the relationships between entities. We will leave the attributes to the logical data modeling stage, so we can focus on the high-level views of the data requirements and avoid being bogged down by details. Now, let's start going through user stories.

As mentioned before, the User entities and Team entities are in a one-to-many relationship, which covers the users and teams themes in the user stories.

And, based on the user story, Create personal board, that was created in Chapter 4, TaskAgile - A Trello-like Task Management Tool, we can see that there will be Board entities and the relationship between the User entities and Board entities is also a one-to-many relationship, as shown in Figure 5.5:

And, based on the user story, Create team board, that was created in Chapter 4, TaskAgile - A Trello-like Task Management Tool, we can discover that there is a relationship between Board entities and Team entities, which is a one-to-many relationship, as shown in Figure 5.6:

In Figure 5.6, the relationship reads like this—A Team may contain zero or many Boards, and a Board may belong to zero Teams when it is a personal board, or one team when it is a team board. Wait, it seems that the relationship has hidden information about distinguishing between a personal board and team board. It would be better that we make this distinction explicit in the diagram by creating two subtypes of Board entities: Personal Board and Team Board, as shown in Figure 5.7:

As you can see, we update the cardinality on Team's end from zero or one to one and only one, because a Team Board must belong to one and only one Team.

This is still not accurate because, based on this diagram, the relationship between User entities and Team Board entities is Each User may create zero or many Team Boards. That is, any registered user can add a board to any team, even those teams that are not created by that user. This is wrong. The correct logic should be that only a team creator can add boards to a team. And it is also wrong in the Create team board user story, which says As a registered user. It should be As a team creator. Let's correct the Create team board user story to the following:

• Title—Create team board: As a team creator, I can add a board to my team so that I can organize the tasks of that team, and we should also use Team Creator as a subtype of User. Figure 5.8 is the revised diagram using subtypes of User and Board:

Based on the diagram, one Team Creator can also create zero or many Personal Boards, which makes sense because a Team Creator is also a User. Or does it really make sense? We will come back to this later. For now, let's move on.

Based on the user story, Add board member that was created in Chapter 4, TaskAgile - A Trello-like Task Management Tool, we can see that there is another relationship between User and Board, which is a many-to-many relationship, as shown in Figure 5.9. In fact, when a user creates a board, that user should become the first member of that board. So, a Board must have one or many Users as its members:

Based on the user story, View board activities that was created in Chapter 4, TaskAgile – A Trello-like Task Management Tool, we can see there is a new type of entity, Board Activity. The relationship between Board and Board Activity is a one-to-many relationship, and the relationship between Board Member and Board Activity is a one-to-many relationship, as shown in Figure 5.10:

In the relationship between Board and Board Activity, the cardinality on the Board Activity end is a one or many cardinality because, when a board is created, there will be a Board Creation Activity generated. Hence, each Board must have at least one Board Activity

Based on the user story, Create card list, that was created in Chapter 4, TaskAgile – A Trello-like Task Management Tool, we can find the Card List entities and the relationship between Board Member and Card List is a one-to-many relationship. And, the relationship between Board and Card List is also a one-to-many relationship, as shown in Figure 5.11:

Here, Board Member is a subtype of User. We use Board Member instead of User, because not every user can add a card list in a board. Only those who are members of that board can do it. If we replaced Board Member with User, this business rule would be missing.

There are no new entities or relationships in the other user stories in the Card Lists theme. Let's move on to user stories in the Cards theme.

Based on the user story, Add card, that was created in Chapter 4, TaskAgile – A Trello-like Task Management Tool, we can find a new entity Card, and its relationship with Board Member is a one-to-many relationship, and its relationship with Card List is also a one-to-many relationship, as shown in Figure 5.12:

Based on the user story, Assign member to card, we can find another relationship between Board Member and Card, which is a many-to-many relationship, as shown in Figure 5.13:

Based on the user story, Add card attachment, from Chapter 4, TaskAgile – A Trello-like Task Management Tool, we can see a new entity Attachment. The relationship between Board Member and Attachment is a one-to-many relationship. The relationship between Card and Attachment is a one-to-many relationship, as shown in Figure 5.14:

Based on the user story, Add card comment created in Chapter 4, TaskAgile – A Trello-like Task Management Tool we can see that there is another type of entity, Comment. The relationship between Board Member and Comment is a one-to-many relationship. And the relationship between Card and Comment is also a one-to-many relationship, as shown in Figure 5.15:

Based on the user story, View card activities, that was created in Chapter 4, TaskAgile – A Trello-like Task Management Tool, we can find a new type of entity, Card Activity. And the relationship between Board Member and Card Activity is a one-to-many relationship, and the relationship between Card and Card Activity is also a one-to-many relationship, as shown in Figure 5.16:

Figure 5.17 is the ER diagram with all of the entities and relationships we have discovered:

If you take a closer look, you can see the relationship between User entities and Board entities is a membership relationship. But, we also have Board Member entities as a subtype of the User entities. As you can see, something smells here. It would become more obvious when we only have those relevant in the diagram, as shown in Figure 5.18:

Let's do a test on the relationship between Board Member and Board based on this diagram. Since we make Board Member a subtype of User, it should inherit the relationship of its supertype. Therefore, the relationship reads like this from left to right: A Board Member may be a member of zero of many Boards. This can't be right because a Board Member must belong to one and only one Board. That's why it is called Board Member. To fix this, we will have to change the have as member/be of member of relationship. However, if we change it, the relationship between User and the Board will be wrong.

So, there is something wrong with this part. If it is not the relationship, it must be the entities. And, based on the test we just did, Board Member entities do not inherit that membership relationship of User entities. It means that they are not a subtype and supertype, even though it sounds true when you say Board Member is a subtype of User. Actually, it is true when they have the Board Member role and User role in the application.