The Design

The database designers at EAB Healthcare have completed the design of the database structures based on the analysis of the requirements for the system and their knowledge of database design. The next step for the team is to figure out the physical structure and deployment of the database ”they will design the storage of the data, including where it will reside and how it will be partitioned. The database designers plan to work very closely with the DBAs on this portion of the database design. Designing the storage is where the lines of responsibilities begin to blur a little in EAB's structure, and the teams need a coordinated effort. For the job, we assume that EAB has chosen a relational database vendor for the project. The database will support all of the functionality already supplied by the database design model and will support anything that the storage design calls for. (Rather than selecting a specific vendor for the case study, we choose to remain fairly open and create elements supported by most databases.) The major functionality that the EAB designers come up with that is not supported in all databases is partitioning of tables, although many database vendors seem to be moving in that direction. If you are working on a project for which the chosen DBMS does not support such functionality, you can still use the design techniques described here, leaving out the partitioning.

Creating Tablespaces

Using a component with the stereotype <<Tablespace>> creates a tablespace in the UML, as shown in Figure 8-5. The database designers for EAB first evaluate the tables that exist in the database design to determine what tablespaces are necessary. The designers also take into consideration all of the existing information that has been gathered throughout the entire design process, in cluding the written requirements and all of the diagrams that have been created to describe how EAB Healthcare as a whole will accomplish the creation of the new system.

Figure 8-5. Component modeled as a tablespace

It is easiest to build tablespaces based on known tables and then to expand them to additional tablespaces, partitioned or combined where needed. The EAB database designers see an obvious place to begin: at the highest level, the MDS itself. The MDS table consists of nine columns, five of which are foreign keys. This table, although consisting of not many columns and therefore few rows, will touch almost every table that exists within the EAB database and at a minimum requires its own tablespace. The database designers create the tablespace called MDS, which is dependent on the MDS table (see Figure 8-6). There are other attribute type values that may be associated with the tablespace, including the drive that contains it, storage size , and minimum and maximum sizes; depending on the database, there can be even more.

Figure 8-6. MDS tablespace and its dependency on the MDS table

The MDS tablespace is a simple example, but the process grows harder as the design continues. Storage modeling is a very iterative process and continues to change even after the database is deployed. Many assumptions have been made, and although they are educated , they are not perfect on the amounts of data that will be captured in the database and how frequently the data will be updated. The database designers are making the first pass at modeling how the storage will be defined. They know they will have to change it somewhat over time. The goal is to make the design as flexible as possible and to keep the plans for increasing the number of residents in EAB Healthcare and for expanding the company in the design.

Next the database designers look at the Resident table. Resident is a very large table that contains data for each person who has been a resident at EAB Healthcare ”both current residents and ones who have left EAB, by choice or possibly by death. Because of all of the data in the Resident table, the database design team decides to partition the data into three tablespaces, dividing residents into groups according to the first letters of their last names : A to H, I to Q, and R to Z (Figure 8-7). Having the data on different tablespaces enables quicker and easier queries. There are no relationships among residents and this makes the partition easier because the designers do not need to worry about splitting related residents.

Figure 8-7. Resident table partitioned across three tablespaces

The design team continues to look at the highest-level tables and uncovers that there are several different service providers for residents, including Nurse, Internal Care Provider, Therapist, and Dietician. These tables are all assigned to the tablespace ResidentCareProvider (Figure 8-8).

Figure 8-8. ResidentCareProvider tablespace and its dependent tables

There is another group of workers that takes care of EAB Healthcare overall but doesn't necessarily have direct contact with the residents; they are known as the administrative staff. The administrative staff includes Administrator, Billing Manager, Medical Records Manager, and Facility Staff. The tables for the administrative staff all belong together since the information that each contains may fall into the other tables' categories and they will often be queried to gether. The database designers create a new tablespace called Administrative to contain these tables (Figure 8-9).

Figure 8-9. Administrative tablespace and its dependent tables

The database design team follows the flow of packages created in the database design activities to continue with the project. The packages consist of Basic Assessment, Background, and Full Assessment. These three packages make up the remaining tables in the database as created in the database design. The team first looks at Basic Assessment, which is really made up of only one table, BasicAssessmentMDS, and is related to the Resident table. The tablespace is called BasicAssessment and includes just the single table (Figure 8-10).

Figure 8-10. Tables and tablespaces for the Basic Assessment MDS

The Background package gets somewhat more crowded. There are five tables that exist within that package. The tables include InvolvementPatterns, EatingPatterns, DailyEvents, and ADLPatterns plus the BackgroundMDS table which brings it all together. The database designers choose to create two tablespaces for this package, one for BackgroundMDS and one called Background for the other tables (Figure 8-11). The BackgroundMDS table feeds into the MDS table and contains a lot of data within itself along with foreign key references from other tables.

Figure 8-11. Tables and tablespaces for the Background package

The Full Assessment MDS gets to be quite complex. There are many tables involved and they contain all of the data that make up the Full Assessment MDS. The Full Assessment MDS is the final piece that makes up the entire MDS, which includes the Background MDS and the Basic Assessment MDS. There are 21 different tables with varying amounts of data captured within each table. Using the Establish MDS sequence diagram and others, the EAB database de signers take a look back at the way an MDS is established (see Figure 8-12). By understanding who has the input into the MDS, how it is created, and what extent of information is needed for each element within the MDS, the database designers can determine how to arrange the data over tablespaces and assign correct volumetric sizing estimates in creating those tablespaces.

Figure 8-12. Establish MDS sequence diagram used to understand interactions in the creation of the MDS

After the intense mining of already captured information and using much of their own knowledge of database design and experiences building several other databases, the database designers make some decisions on how to build the database storage in tablespaces. The team determines that there is a need to have five different tablespaces to represent the Full Assessment MDS. The tablespaces are called FullAssessment (Figure 8-13), MDSSet (Figure 8-14), Physical (Figure 8-15), Treatments (Figure 8-16), and Mental (Figure 8-17).

Figure 8-13. FullAssessment tablespace

Figure 8-14. MDSSet tablespace

Figure 8-15. Physical tablespace

Figure 8-16. Treatments tablespace

Figure 8-17. Mental tablespace

Determining Schemas and Databases

The database designers have been very busy building all of the tablespaces, setting their properties, and discovering all that is needed in the database storage. The next part is much simpler: they determine the schema and database to which they assign the tables and tablespaces. Since the design of the database is fairly small, they decide to use just one schema and one database and to design the database that way. The schema is called MDSSchema and the database is called MDSDatabase. To model these elements, the designers use components and dependencies to visualize the databases and schemas on the diagram. The database designers build a comprehensive diagram that shows all tables associated to their tablespaces as well as an overview diagram (Figure 8-18) that visualizes the tablespaces and their associations to the schema and database.

Figure 8-18. Overview of how the tablespaces are related to the schema and database

Designing the Hardware Needed

The database designers at EAB are not hardware experts and don't claim to be, so they give the DBAs and the information technology team the diagrams to help these people visualize what data is being captured. The database designers also provide reports based on those diagrams to explain what the tagged values of the tablespaces include in terms of plans for growth over time. The database designers do not really set up specific diagrams for the others to use, but they give their input so that the information technology team can work its own magic and make the decisions on drives and number of servers needed to build the database correctly. Through use of the UML, the information technology department will continue to add to the diagrams provided by the database designers and will build requirements for the hardware that is needed. They will in turn feed that information back to the database designers to ensure that the technology team correctly understood the diagrams and that the tagged values on the tablespaces are still correct, in case any changes were needed based on the servers and drives available.