The database design team first focuses on creating tables from existing classes. This ensures that all of the classes that were created and marked as persistent based on the requirements that were gathered are moved forward into the database. Once that has occurred, the team can start looking for ways to optimize the database, beginning with how to handle tables that were created based on inheritance relationships in the class model and classes that took part in many-to-many relationships that have to be split using association tables. From there the database design team will begin to ensure uniqueness of the tables and enforcement of such items as rules using constraints on the database. The database design team at EAB Healthcare doesn't really work much with triggers and stored procedures. They are handled primarily by the database administrators and will therefore be worked on later, after the database design model has been fairly locked down.
EAB has done a good job of capturing the requirements and turning them into classes. Working together, the development, business, and database teams have gone through each of the classes and marked the ones that are needed as persistent. The first job for EAB's database designers in creating tables from classes is to decide what packages to work with and to begin the transformation to tables.
The database designers for EAB begin to move items into the database, working first with some of the actors that have information required for the MDS. Based on the government requirements, EAB has identified three primary MDSs that EAB has to be concerned with: the Background MDS, the Basic Assessment MDS, and the Full Assessment MDS. Each of these is important and feeds information to the other. When building the database, we want to begin with the lowest common denominator and build from there. This ensures that the basics are covered. As the database designers continue gathering the information that needs to be captured, they can determine whether each item needs its own table, can fit into an existing table, or is already being captured for another purpose.
The database team looks at the requirements for each MDS type to determine what information is needed to complete each type. It is almost like thinking of each MDS as some type of form or screen and considering what information is needed to fill in that screen and what is the best way to organize the information in table and column structures to store the information.
The first set of information captured about a patient in the initial MDS comes from the background information obtained at admission. The Background MDS gathers the initial information from an incoming resident as he or she registers at EAB and can be used to fulfill much of the more detailed MDS later. The background information is used to understand who the resident is and some of his or her habits, problems, and reasons for coming to EAB.
Based on the needs of the Background MDS, the information is broken into two categories:
The database design team creates a set of tables based on the typical information needed for these two categories, gathering the appropriate information from the classes and actors that already exist. The Resident actor fulfills most of the demographic information needed and the customary routine information is gathered from various other classes. Figure 7-9 shows the first database diagram the team creates to support the Background MDS.
For the transformation from the logical design model to the database de sign model, the team creates and uses some domains, as shown in Figure 7-9. The primary domain is Boolean and is mapped to a CHAR(2). Also attached to that domain is a check constraint that has a valid value of y or n. This helps to enforce that the logical type of Boolean is transformed correctly to a database type of CHAR(2) and has rules to enforce its properties.
The next set of information that is tracked and may have a set of circumstances for when it is used to feed the Full Assessment MDS is the Basic Assessment Tracking (BAT). Some of the information for the BAT will come from tables that have already been created in the Background MDS and some will be new. The database designers for EAB create diagrams to demonstrate each type of form. Tables can be included on many diagrams but exist only within one model. A diagram is a basic way to display information visually to make it easier to understand and group in a logical way.
The EAB database designers now focus on the BAT and transform classes to tables that pertain to the BAT. There is really only one class that becomes a table for the BAT, the Basic Assessment MDS class. The other table used in the diagram is the Resident table (see Figure 7-10).
The final set of information needed to complete all of the information for the different types of MDSs is the Full Assessment MDS. Just like the previous sets of information, the Full Assessment MDS includes some information already captured mixed with new data needed only for the Full Assessment MDS. The EAB database designers begin looking at the information already captured in the logical design that is needed to create the Full Assessment MDS and decide what to transform into the database design as tables and columns. Once the database designers have completed their assessment of the classes to become tables, they create the tables as needed and make sure that column properties are correct based on the business rules already uncovered and the needs of the database design.
The Full Assessment MDS contains many tables, columns, and relationships梕verything from the prior MDSs as well as everything from its own MDS梐nd therefore is quite crowded. The EAB database designers decide to build several diagrams to display the Full Assessment MDS to make it easier to read and understand. One diagram has a complete overview of the entire MDS but contains only table names and relationships (Figure 7-11). One diagram contains just the new tables that are involved in the Full Assessment MDS and not the tables involved in other MDSs (Figure 7-12). The database designers create several other diagrams for different views as well.
The database designers make several changes to the model to help make it more easily read and optimized. First they create alternate keys for the Background MDS, Full Assessment MDS, and Basic Assessment MDS, enabling the designers to migrate the alternate keys rather than all of the primary keys. This is a big change to the model because of all the identifying relationships from the child tables to each of the different types of MDS tables; there would have been many foreign keys migrated since each had become a primary and foreign key in the child MDS tables. Using the alternate keys instead of all primary keys means that only one column migrates as a foreign key from the MDS type tables to the MDS table (see Figure 7-13).
The database designers begin to look at the way tables will be queried so that they can include indexes on the tables as needed. There is a lot of information captured in the MDS tables and therefore a lot of data will be required in different queries. The EAB database designers look at each table individually to determine what indexes need to be created. (For this example we look at just the MDS table and uncover the indexes that are needed for it.) The EAB database designers decide to create a few different indexes, one for each of the foreign keys and one composite index that includes both the creationDate and lastUpdate columns (see Figure 7-14). Some databases automatically create indexes for each foreign key, but since EAB has not yet chosen a platform, the database designers include indexes on the model to ensure that the indexes will be created at database generation time.
To ensure that the data collected within the database is correct, the database designers create check constraints on columns and tables. The check constraints are restrictions on the database that are enforced with the implementation of the rule within the constraint. For this example we again focus on only one table, although many tables and columns will have check constraints as signed to them. Since the Resident table has exposure to almost every facet of the database design, we have chosen to work on this table for the example. Some of the constraints can be reused in many different tables as long as the constraints apply to the context. The constraints may be created based on do mains that are assigned to the columns within the table, and others are assigned specifically to that column but may be reused on a more global basis.
There are four check constraints on the Resident table: Gender, Birthdate, MedicareResident, and MaritalStatus (see Figure 7-15). Gender is a valid value type of check constraint with the valid values of M or F. Birthdate is a constraint with a rule that enforces that the birthdate must be prior to the admission date. MedicareResident and MaritalStatus are also valid value check constraints. MedicareResident will have the valid Boolean values of Y or N and MaritalStatus values will be M, D, S, or W.
Database views are a physical implementation of virtual tables used for reasons including performance and security. The database designers at EAB choose to use views so that they can secure information within certain tables that many different people need to access yet make some of the very secure information available to only a few people. The other set of views takes information that is often queried from various tables at the same time and puts it together in a view as a virtual table to enhance performance when querying (thus the select statement can query one place and get all needed information). There are many views in the database, but we again focus on only a few.
The view that the EAB database designers start with is on the Resident table and is called the PublicResidentView (see Figure 7-16). This view prevents most users from viewing such information as the patient's social security number, education level, and marital status. There are many groups in EAB Healthcare that do not need this information (and having this information can possibly cause bias toward the patient), so the view excludes these columns. Another view is based on the Background MDS for the information that is needed most often by the accounts receivable team members. They do not need all the information, only some from many tables within the Background MDS and its related tables that supply information for the Background MDS. Therefore, it is much easier and quicker for the application to query one place to get the needed information. This view is called ARBackgroundView (see Figure 7-17).