- Your Project: A Time Entry and Billing Database
- Determine the Requirements of the Database
- Introduction to Database Modeling
- Model the Time Entry and Billing Database
- What You Have Learned
This chapter is from the book
This chapter is from the book
This chapter is from the book
Model the Time Entry and Billing Database
Now that you have a good understanding of what database modeling is, you can employ the techniques to your current project—the Time Entry and Billing Database. Through the course of interviews and the analysis of business documents and forms, you have identified the following entities:
- Clients
- Employees
- Cases
- Invoices
- TimeEntryDetail
In the course of your analysis, you have identified the following relationships:
Clients can have one or more cases.
Clients can have one or more invoices.
Invoices are made up of one or more TimeEntryDetail records.
Time is charged to a case.
Employees charge time to a case.
Figure 3.16 shows the database model at this point in the development process. As you will soon see, there are already a few wrinkles to contend with.
){kind=link}
Figure 3.16 Version 1 of the Time Entry and Billing Database model consists of five entities.
Editing the Text
Have you noticed the shape in the diagram in Figure 3.16 that contains descriptive information about the chart? The shape is called the database title and is located in the Design Symbol toolbox. After you place the database title shape onto the design surface, each text block can be edited by following these steps:
Click the database title shape; this selects the entire shape. A green selection marquee will surround the border of the shape.
At this point, you can select each rectangle contained within the database title shape. As you select each contained rectangle, a gray selection marquee surrounds its border.
Press the F2 key. This highlights the text, allowing you to modify the text.
Refining the Design
The design so far looks good. Is it complete? Given that this is the first iteration of the design, the answer most likely is no. This case is no different. Did you notice relationship number five is marked with an asterisk? Although this relationship might not seem ambiguous at first, it is very ambiguous. Let's look at the relationship "Employees charge time to a case" a bit more closely.
Suppose in the course of interviewing, you discover that an employee must be assigned to a case before the employee can charge time to a case. Does the current design of the database have a direct relationship between cases and employees? No, indirectly a relationship exists. From the Case entity, you can find employees who have charged time to the case by going through the TimeEntryDetail entity. As long as all employees associated with the case have charged time to the case, you can find all the employees associated with a case. What if, on the other hand, you need to get a list of all employees associated with a case and you know that some employees working on the case have not charged time? The short answer is that you are out of luck!
It is important to emphasize that relationships between entities have to be supported regardless of whether specific instances of data exist. In other words, even if an employee has not charged time to a case, that employee has to appear in a list of employees for that case. Figure 3.17 shows the refined design.
){kind=link}
Figure 3.17 Version 2 of the Time Entry and Billing Database model supports a direct relationship between employees and cases.
Do you see another problem? Based on this model, when it comes to associating employees with cases, you have the following situation: Each time an employee is associated with a new case, a new employee record for that employee must be created. This results in duplicate employee records. It would not take long for your database to become cluttered with a lot of redundant data. When it comes time to create queries and reports, your task will be difficult. What happens if an employee changes her name and she is associated with 10 cases? Ten different employee records would have to be modified. You have enough knowledge about relational databases to know that this scenario would be unacceptable. One of the main design goals with relational databases is to achieve data consistency by way of reducing data redundancy. So, then, how do you go about solving the problem?
Do you recall in Chapter 2 when the topic of many-to-many relationships was discussed? That is exactly the issue that exists in this case. Employees can be associated with one or more cases, and cases can have one or more employees. In Chapter 2, you were shown that breaking a many-to-many relationship down into two one-to-one relationships addresses this situation. To solve the problem, a new intermediary table, called a junction table, must be added to the model. Junction tables are also called many-to-many resolver tables—what you call it is not nearly as important as what the table accomplishes. Junction tables enable you to facilitate data consistency by way of reducing data redundancy. Figure 3.18 shows version 3 of the database model that contains the new junction table and modified relationships between the Case and Employee entities.
){kind=link}
Figure 3.18 Version 3 of the Time Entry and Billing Database model supports a many-to-many relationship between cases and employees.
There is still a little more work to accomplish before the first draft of the database design can be considered complete. As you continue your analysis, you find that multiple clients can be associated with a case. Because of this change, the granularity of associating a TimeEntryDetail record with a case, an employee, and an invoice is not enough. Although it is true that the TimeEntryDetail record has to roll up to one case, one invoice, and one employee—because multiple clients can be associated with a case—the TimeEntryDetail entity must carry the ClientID as a foreign key. See Figure 3.19 for the complete first version of the Time Entry and Billing Database.
){kind=link}
Figure 3.19 Version 4 of the Time Entry and Billing Database model represents the completed first draft of the database model.