The Design

It is time for the database design team at EAB Healthcare to make some strategic decisions. What classes from the logical analysis model will be transformed into the data model? How will they become tables? Without consideration for the specific relational database management system (RDBMS) to be chosen or performance issues, it is time to know exactly what data will be captured. In the previous chapters, the business analysts, application developers, and database designers worked together to understand the requirements for the system and what problems it would solve. Now the database designers take their piece and begin moving forward. The process follows a basic flow, but as always, development is an iterative process, and there most likely will be steps that begin now but are continued later, during or after other processes.

Making Entities Persistent

Members of the database design team have been working with the representatives from other parts of the development organization to define elements of requirements, business rules, classes, and more. Now the database design team will work with or at a minimum consult with the other teams to look specifically at those classes and decide what is to be marked as persistent and transformed to the data model. When using a UML design tool, the persistent tagged value can be set within the tool. It may even support transformation to a database design automatically or even the generation of Data Definition Language (DDL) based on the classes marked as persistent.

There are a lot of classes that have been defined throughout the process of gathering requirements and creating the logical design. Because the entire business of EAB is so large and there can be many different integrated systems working together, the database team thinks it best to look specifically at what is needed to support the MDS, working just on that issue for this first task. The MDS most likely will be contained within its own schema and possibly linked to other schemas as well. Although the focus for now is on MDS-specific information, there will be more global entities that will also be used outside of the MDS and are more global entities, for example, residentName. There will probably be a schema that covers generic resident information that will contain the resident's name , but we need to link to that to complete the MDS. So the database design team, although looking at the specifics needed for the MDS, either may capture this global type of information and link to existing systems or may have to build additional tables or schemas to support it. We will see the results of this in upcoming chapters, but for this chapter we focus on all that is needed to build a complete MDS.

The MDS is divided into three basic sections, the Basic Assessment Tracking Form, the Background Information, and the Full Assessment Form. Although there are three forms, the information for them will be captured together in one place. The forms are to be created in applications or the user interface, but it is all the same information and therefore will be captured in one database. It is important not to separate this information into multiple databases, so that if new forms must be developed that include information from multiple areas, it is much easier to reuse the structures already available and working. Looking at the classes already created, it is a fairly simple process to complete this first step to understand and define which classes will be turned into tables and columns .

By just working through the diagrams in the order they were created, the database designers make some decisions on which classes will be marked persistent:

Resident	Physician	Nurse
Insurance Company	Payor	Basic Assessment MDS
MDS	Guardian	Background MDS
Full Assessment MDS

To drill down on the MDS, the database designers look at what is needed specifically for each MDS. The Background MDS and Basic Assessment MDS are fairly simple and mostly covered by the classes already marked, but the Full Assessment MDS is much more detailed and requires a lot of information. Therefore, many more classes must be marked as persistent. The Full Assessment MDS covers the resident's entire history, the family history, and current problems (both in physical and mental health); daily routines; and more. We will not list each of the classes separately, but they will be marked and used later in the database.

Transformation of Attributes

There are choices to be made on which attributes will eventually become columns in the database. There are attributes that will not become columns and columns that will be created once the database design model begins to take shape. Different methodologies have their own theories on primary and foreign key participation in the logical analysis model, but for our model, we will have a mix of primary keys in the logical analysis model. There will be some primary keys, where they are needed, but some will be created in the database design model where the identifier is not needed for any other reason than to identify the table. Foreign keys will not exist in the logical analysis model. They will be created based on the relationships that appear in the database design model.

The database design team at EAB has been working closely with ”and some members have even been a part of ”the business analyst team. Now the de sign teams are reaping those benefits. The logical analysis model is well attributed and has the major information needed to move forward into the database de sign. The attributes at this point are all needed in the database design, so there aren't any marked as derived, although there may be some created by the application design team later that will appear only in the application. There are some classes that contain attributes that will become primary keys, for example, the business actor that will become a Resident table. Resident has an at tribute called residentID, which is a unique identifier for each Resident and therefore will work perfectly as our primary key in the database design (see Figure 6-5). To mark an attribute as a primary key, the attribute can be marked with a tagged value, Part of Object Identity. This means that the attribute residentID is a part of the object identity for this particular class and it should become the primary key in the database design.

Figure 6-5. Resident class, fully attributed

We must also consider types. Logical analysis models generally contain at tributes that have generic types or analysis types. These types are fairly descriptive but not specific to any implementation. There are some basic types; however, you can always add some of your own based on corporate standards or just personal or group preference. Many of the generic types made available in most tools are shown in Table 6-1.

Another way to use types is through the use of domains. Domains provide a mechanism to create user-defined types that are reused throughout the models. The domains contain all information that is associated with an attribute or column with the exception of the attribute or column name. Domains can also be very helpful in the transformation between the logical and database design models. Using domains to their fullest potential, you can define their properties for all types of models, including logical, database, or application, making the transformation between models almost seamless. In the next chapter we cover the modeling of domains and their usage in more detail. The EAB teams have not yet employed the use of domains within their design, but they have taken advantage of the many generic types.

EAB Healthcare has created a mapping of the logical types to the specific database types (Table 6-2). The database designers will ensure that when the transformation to the database design model occurs, the mapping will remain intact. The mapping of types is a very important function because if you do not use each generic type correctly, the data being captured may not match what was originally called for in the requirements. There is a need to map not only to the ANSI types but also, more importantly, to your specific database engine. It is quite helpful, though, to map to the ANSI types first because they generally map well to any database engine available. The importance in the use of do mains becomes more apparent here as well. It becomes obvious that if there is a need to manage the mappings, especially the length, precision, scale, and other properties of a column's datatype, it is nice to have something that is reusable and able to be defined exactly as planned, rather than a generic mapping.

Table 6-1. Generic Types and Their Descriptions

Generic Type	Description of Generic Type
Boolean	Used to represent the logical values of True or False
Currency	Used to declare variables capable of holding fixed-point numbers with 15 digits to the left of the decimal point and 4 digits to the right
Date	Used to hold date and time values
Double	Used to declare variables capable of holding real numbers with 15 “16 digits of precision
Integer	Used to declare whole numbers with up to 4 digits of precision
Long	Used to hold numbers with up to 10 digits of precision
Single	Used to declare variables capable of holding real numbers with up to 7 digits of precision
String	Used to hold an unlimited number of characters

Table 6-2. Generic Types Mapped to ANSI SQL 92 Datatypes

Generic Type	ANSI SQL 92 Datatype
Boolean	Bit
Currency	Decimal
Date	Date
Double	Double Precision
Integer	Decimal
Long	Decimal
Single	Decimal
String	Char