18.2. Creating the Database
When you start to create any database, you need to move the physical world tasks and items, in this case the boiler information, over into the electronic world of the database. To be a good backend or data store, the database, must fulfill several business requirements, in this case to answer the following questions:
What boilers are out of the predefined specs?
How often is the boiler falling out of spec?
Which operators are finding the most boilers out of spec?
How is the boiler system behaving as a whole?
How long is it taking the operators to complete the boiler inspections?
Can the InfoPath form expedite data collection?
To answer these questions, a database was created to maintain boiler information. To work with the data in the tables, a plan was created that includes these major tasks:
Designing the Database using Microsoft Access to support the InfoPath frontend
Designing the XML Web service to deliver the data to the clients
Designing the InfoPath document to display or collect data from the Web service
These tasks can be accomplished using a wide array of technologies. For this case study, Access was chosen as the data store for the simple ease of use, without incurring the cost overhead of SQL Server.
The preceding bullet points directly reflect how the solution is broken up into three different logical steps in order to create the best overall solution utilizing the best patterns and practices.
You may find that creating the database first is not where you wanted to jump in. Rest assured that if you start creating an InfoPath form without finely detailed planning, which includes the database, you will run into many problems and your solution will take twice as long to create.
InfoPath was designed to query and populate an existing data store. Unlike working directly in Access, the best practice is to create the complete database first then work on the client side of the application. This will make the InfoPath part of the solution very easy and less time-consuming to create.
18.2.1. The BoilerSystem Data Model
Database modeling and architecture are definitely out of the scope of this book, and there are many books already published covering these two areas extensively.
To keep this case study fairly straightforward, the typical one-to-many type of relationships in the database that will support most real-life situations will be used. If you are interested in more advanced database architecture subjects visit www.wrox.com, and you will see many books on Microsoft Access and/or SQL Server 2000. Following are the tables and fields outlining the BoilerSystem database:
18.2.1.1. BoilersOperatingLog table
The first table created is the table to hold the actual boiler inspection data. During initial analysis and gathering of the business requirements the BoilerOperatingLog table was found to need 12 Fields. The fields are:
| Field Name | Description |
|---|---|
| BoilerLogID | Autonumber field that will uniquely identify each boiler log record. |
| BoilerID | Look up field to the Boiler table. |
| LogDate | Date the inspection took place. |
| LogTime | Dollar time the log was submitted. |
| ShiftID | Number of items included in the order. |
| OperatorID | Lookup field to the Operator table. |
| SteamDrumStatus | Status of the boilers steam drum. |
| SteamDrumPSIG | Status of the steam drum PSIG. |
| Excess | Field to hold the excess PSIG data. |
| SteamLoad | Steam load of this particular boiler. |
| SteamCycles | Steam cycle data goes here. |
| Comments | Any comments the operator may want to make on this inspection are stored here. |
18.2.1.2. SystemLog Table
The SystemLog table will hold the data that is particular to the boiler system as a whole. This will store important information concerning the building and the overall health of the system.
The fields are:
| Field | Description |
|---|---|
| SystemLogID | AutoNumber field that will uniquely identify each system log record. |
| SteamHeaderPSIG | Steam header PSIG. |
| SteamFeedWaterHeaderPSIG | Steam water PSIG. |
| DearatorPSIG | Dearator PSIG. |
| DearatorTemp | Dearator temperature. |
| FMACOutsideAirTemp | FMAC outside air temperature. |
| BlowDownOnlineBoilersLWCO | Status of the boilers steam drum. |
| ShiftID | Field to hold the excess PSIG data. |
| LogTime | Steam load of this particular boiler. |
| OperatorID | Steam cycle data goes here. |
| Comments | Any comments the operator may want to make on the system inspection are stored here. |
18.2.1.3. Lookup Tables
The following tables are considered lookup tables, they hold static data that doesn't really change that often and are there mostly to provide information to fill in combo boxes or to look up data in.
18.2.1.3.1. Boilers
This table will hold the individual boiler information. The fields are:
| Field | Description |
|---|---|
| BoilerID | AutoNumber field that uniquely identies each boiler. |
| BoilerNumber | Boiler number. |
| BoilerDescription | Description of the boiler. |
18.2.1.3.2. Shifts
This table will hold the individual boiler information. The fields are:
| Fields | Description |
|---|---|
| ShiftID | AutoNumber field that will uniquely identify each shift. |
| ShiftName | Shifts name, used for display purposes. |
| TimeStart | Time the shift starts. |
| TimeEnd | Time the shift ends. |
18.2.1.3.3. Operators
This table will hold the individual boiler information. The fields are:
| Field | Description |
|---|---|
| OperatorID | AutoNumber field that will uniquely identify each operator. |
| OperatorName | Boiler number. |
| OperatorNumber | Description of the boiler. |
18.2.2. The BoilerSystem Relationship Model
When working with more than one table in a database, it is usually more efficient to create one-to-many relationships between the tables. This allows for a more normalized database structure and allows to greater flexibility when creating the Web service and client-side application. Figure 18-2 shows the design of the database with the relationships illustrated:
Figure 18.2. Figure 18-2
