Now, let's create the logical data models of TaskAgile. In this stage, we will add attributes to each type of entity in the conceptual data model by going through user stories by theme and implementing the relationships between entities, as well as the subtypes we used.

Based on Users theme and Teams theme, we create the following logical data models:

As you can see in Figure 5.20, for User entities, we use surrogate key ID as its primary key because even though Email Address and Username can be used to identify a user, they can be changed. The system-generated ID will always stay the same. It is the same for Team entities. And the User ID attribute in the Team Creator entities is a foreign key. In the diagram, PK is short for Primary Key, and FK is short for Foreign Key. And, in Team Creator entities, the Team ID and User ID attributes form a composite key that can be used to uniquely identify a Team Creator.

Based on the user stories in the Boards theme, we update the logical data model, as shown in Figure 5.21:

In the conceptual data model, we have Personal Board and Team Board as subtypes of Board. Here, we only have Board. In the Board entities, we add an attribute, Type, to distinguish Personal Board and Team Board, and a foreign key, User ID, to build the create relationship between User entities and Board entities. And the foreign key, Team ID, is to build the one-to-many relationship between Team entities and Board entities.

As you can see, both Board entities and Team Creator entities have the foreign key Team ID and foreign key User ID, that can be used to identify the user who creates the team. However, based on this logical data model, the user who adds the board doesn't necessarily have to be a team creator. It can be anyone. If you want to enforce the constraint that only team creator can add a board on the database level, you can change the design, as shown in Figure 5.22:

As you can see, we add attribute ID to Team Creator entities and foreign key Team Creator ID to Board entities. This will make sure all of the boards are created by Team Creator. However, this could be the root of the rework when we need to allow other team members to create boards. The design in Figure 5.21 provides extensibility, which makes it a better design from this perspective. As a tradeoff, we can enforce the constraint in the application's code. Another reason we should not use Team Creator ID in Board entities is that there will be an extra join of tables to query boards by User ID.

You might have noticed that in Figure 5.21, for Boards that are personal, the value of attribute Team ID will be null, and this is allowed. Foreign keys can have null values.

The following Figure 5.23 is the logical data model with Board, Board Member, Board Activity, and User entities. For simplicity, we will keep Team Creator out of the diagram:

In Board Member, foreign key Board ID and foreign key User ID form a composite key that can be used to uniquely identify a board member. We use this composite key in Board Activity entities. And, between Board Activity entities and User entities, there is a one-to-many relationship. This is not the same as how it is in the conceptual data model, in which it is the Board Activity entities and Board Member entities that have the one-to-many relationship. The reason we make this adjustment is that we don't want to add a primary key ID to Board Member and use that primary key in Board Activity since Board Activity already has the Board ID attribute and User ID attribute, which is a composite key that can be used to identify a board member.

And, in the conceptual data model, Board Member has relationships with Card List, Card, Comment, Attachment, and Card Activity. For the same reason, we will use the composite key, <Board ID and User ID>, to build those relationships.

The following Figure 5.24 is the logical data model with Card, Card List, and other related entities:

In this diagram, as you can see, we use the Board Member composite key (Board ID, and User ID) in Card List, Card, Attachment, Card Activity, Assignment, and Comment entities. But this has caused data redundancy, mainly with the Board ID. The reason Board ID is redundant is because it can be derived from other attributes. For example, the Board ID in the Card entities can be derived from Card List entities through the Card List ID in the Card entities. Another issue with this design is that when we need to implement a feature in the future, for example, moving cards between boards, we will have to update all of the Board ID in all those entities, which will cause not only the performance issue, but also data inconsistency. And the effort to solve these issues in the future would be significant.

So, let's remove Board ID from Card, Attachment, Card Activity, Assignment, and Comment. This will leave the User ID in these entities. And the relationships between Board Member and these entities will be moved to User entities. And because of this, we will need to enforce the constraint that only a board member can perform these related actions in the application code. With this adjustment, our data model will have no redundant Board ID and will also be extendable.

And, as you can see in Figure 5.24, we add the Archived attribute to entities that need to support being restored after removing. That is, when a card has been archived, the value of the Archived attribute will be true and the card will not show up in the UI. And a board member can restore it from the activities list of that board.

One more thing before we do the removal of Board ID. Let's take a second look at Board Activity, Card Activity, and the user story, View board activities, which goes as follows:

As a board member, I can view activities of everything in that board so that I can understand what happened within that board.

This description is vague. What does everything include? It needs to be specified. Such is the moment when the data modeler needs to have an in-depth discussion with the business specialist.

For this version of TaskAgile, let's add the support of tracking the following activities:

And, if we use Board Activity to refer to the first two activities, Create board and Add member to board in the preceding list, we will also need to have another new type of entity, Card List Activity, to refer those activities to card lists. And, in this way, we will have three types of entities: Board Activity, Card List Activity, and Card Activity.

This separation will cause issues when we need to pull data from three entities to render a list so that board members can view activities of everything in that board. For example, if by default a board member can view the latest 15 activities, we will need to pull 15 activities from each of these entities and combine them into a single sorted list, and then return only the latest 15 items as the result. This still seems manageable. But, how about getting activities from 16 to 30? The complexity of the implementation and performance issue is a sign of a bad data model design.

The following figure is the revised logical data model:

As you can see, we introduce Activity entities that will store activities of all the actions we listed, and also card comments. In this way, we can render card activities and card comments in the same timeline with much simpler queries. The Type attribute of Activity entities is for indicating if a record is an activity log, or if it is a card comment. And, the Card ID attribute is a foreign key, and the value of this attribute will be null if an activity is not related to a card. The Detail attribute will contain detailed data that is stored in JSON format. Since we use these generic Activity entities to store all the activities as well as card comments, we don't need Comment entities.

Let's put all the entities in one diagram, as shown in Figure 5.26. And, in this version, we add foreign key User ID to Team entities and remove Team Creator entities. The reason for that is because Team entities and Team Creator entities are a one-to-one relationship, and there is no other additional attribute beside User ID. One benefit we get from this change is that we can avoid a join of tables to query teams by User ID.

And, as you can see, there are many differences between the conceptual data model and the logical data model. Some of the constraints that we have in the conceptual data model have to be moved to the application's code so that we have better extensibility in the design as well as performance improvement with fewer joins of tables:

By now, we have finished conceptual data modeling and logical data modeling by taking a top-down approach, that is, we build the high-level conceptual data model first and then based on that build the logical data model. And now, let's create the physical data model based on the logical data model.