Applying "Digital Hub" Concepts to Enterprise Software Design, Part 2

In my previous article, I described a scenario where teams of scientists and students would collaborate around some common weather data. We have been tasked with building a software system that allows them all to perform their unique jobs with this set of data. Let's design and create a database that will store the weather data as it is recorded.

Before we dive in, let's think about our design options. If you are not a database administrator or designer, you might be tempted to think of a database as if it were a spreadsheet. After all, database tables have rows and columns, right? If we were to design our database with this paradigm in mind, we might build just one table that contains a column for each type of weather data; perhaps a column for recording the temperature and a column for wind speed. We might also want to collect things like the date of the entry and the name of the scientist who entered the data, so we would create a column for them, too. This type of database is pretty simple to build, and that is what makes it so tempting.

Close your eyes and imagine what will happen if we went down the road with this simple one table design.

Related Reading Cocoa in a Nutshell A Desktop Quick Reference By Michael Beam, James Duncan Davidson

You would probably design and build the database in record time. Everyone in the office would cheer and want to take you to lunch. Development would start immediately on all kinds of cool applications that use the database as a hub. So far, so good. Right? Well doom lurks around the corner. You heard right. Doom!

Six months down the road, thousands of weather samples have been entered into the database and the whole team is deeply focussed on their respective jobs. One of the scientists is about to make a major leap forward in the field of weather prediction! She just needs one little thing. (Uh oh.) Her theory is based on the connection between temperature and atmospheric pressure, but the database is not tracking atmospheric pressure. This new piece of data needs to be collected, stored in the database, and analyzed for all measurements going forward.

Now what are you going to do? It is not as simple as adding an extra field to the table. All of the components that interface with the database will need to be updated, tested, and deployed all over again. And you better make backups, because changing tables in a database full of information sounds risky. At best, things are going to be down for a while, and you are going to have to deal with versioning issues between software, XML, and database schemas. Yuck!

Luckily, you can make decisions today that will avoid your future doom. Instead of hard-coding the measurement types as columns in the database, we will create a table that stores the descriptions of each types as data, not hard-coding it in the schema.

In fact, we will build three tables. The first will hold a list of basic data types that our client applications should know how to validate, such as integer, decimal, and string. The second table will hold measurement types with labels such as temperature, wind speed, and precipitation. Each measurement type will link to a data type. For instance, a temperature could be stored as a decimal number. In other words, the definition of the temperature type would link to the decimal number type. Finally, our third table will store the actual measurements. Each piece of measurement data will have a measurement type associated with it.

With this three-table schema, we will be able to add new measurement types on the fly. We can design our XML and client applications with the same degree of flexibility, which is exactly what we will do in future articles.

Building the Database!

Install xCode, WebObjects, and OpenBase

Before we get started, you will have to make sure that you have the required software on your Macintosh. Though the concepts should be applicable on any platform, this tutorial is based on Apple's Xcode and WebObjects, as well as the OpenBase database. You can get free or trial versions of all three applications from the companies that make them (follow the links). Follow the manufacturers' instructions to download and install each of them, and then we will continue with the tutorial.

First create a new database:

1. Launch OpenBaseManager. You can find it on your hard drive in /Applications/OpenBase/.
2. Install a demo license if you have not done that yet.
3. Click the icon named Local on the left side of the window.
4. Choose New Database in the Action menu.
5. Name the new database Weather and click Create.
6. A window of preference settings for your new database will appear. Make sure that Start Database at Boot is selected and then close the window, saving your changes when prompted.

7. Highlight your new database by clicking on its name. Then choose Start Database in the action menu. Alternatively, you could press the play button next to your database. The little globe next to the database name will turn green when your database is running.
8. Quit OpenBaseManager (the database will continue to run in the background).

Create an EOModel

There are many tools you can use to build a database, but since we are going to use WebObjects on this project, we will use EOModeler. EOModeler is an application that ships as part of WebObjects and is used to map a database to Java Objects. Conveniently, if we design a database schema in EOModeler, it can generate the SQL code to build the actual database for us.

1. Launch EOModeler. If you have installed xCode and WebObjects correctly, you can find it in /Developer/Applications/.
2. Create a new model by choosing New in the Model menu.
3. You will be asked to select a database adaptor. Choose JDBC and click Next.
4. The next window allows you to tell the model how to connect to your database. Since we have not created a name or password for our database, all you need to enter is the URL and click OK. The URL is jdbc:openbase://localhost/Weather.

5. The rest of the options can be left to their default values, so click Finish.
6. Save your EOModel with the name Weather.

Next, we are going to create the tables discussed in the introduction and implement our design in EOModeler, which is creating a description of the database that we will then use to build the actual database. We're going to go quickly through the steps of creating a model. For more detail on using EOModeler, see Apple's excellent WebObjects documentation on their developer web site.

Instructions for creating entities (entities in EOModeler represent tables in the database):

1. With your model open in EOModeler, choose Add Entity from the Property menu.
2. The Entity should show up in the left frame of the window as a child node to Weather. Select it and choose Inspector from the Tools menu in order to show the options available for this Entity.
3. Type DataType for the name and DATA_TYPE for the table name. You can leave the class set to the default value of EOGenericRecord.

4. Add an another entity with the name MeasurementType and table name of MEASUREMENT_TYPE.
5. Add the last entity with a name of MeasuredData and a table name of MEASURED_DATA.
6. Your EOModel should look like this:

Creating ID Attributes (representing database columns):

1. Highlight the DataType entity in the left frame of EOModeler by clicking it.
2. Choose Add Attribute from the Property menu. Your new attribute should show up in the main window and in the Inspector. If you have closed the Inspector, please open it again before proceeding.
3. With your new attribute selected, set the name field to dataTypeId and the column field to DATA_TYPE_ID. Set the external type to long and the Internal Data Type to Integer.
4. In the main frame of the window, make sure that the key icon is showing next to your ID attribute and that the diamond icon is not showing. You can click the space next to the attribute to toggle this setting. See the screenshot below to make sure that you have it right.

5. Create an ID column for the other two tables. The external and internal types are the same. Follow the same convention for the names, too: use measuredDataId and measurementTypeId for the names. Use MEASURED_DATA_ID and MEASUREMENT_TYPE_ID for the columns. In each case, make sure the key icon is showing and the diamond icon is not.
6. Save your work.

Creating Attributes to store data:

1. Highlight the DataType entity in the left frame of EOModeler by clicking it.
2. Choose Add Attribute from the Property menu.
3. Set the name of the new attribute to name and the column to NAME.
4. Set the external type to char and the internal data type to String. When you select String as an internal type, a field will appear that allows you to set an external width. Type 255 in this field.

5. For this attribute, make sure that the key icon is not selected and the diamond is selected. Note: This should be the default and is the opposite of the ID columns.
6. Add a name attribute to the MeasurementType entity that is identical to the one you just created. In fact, you can use copy and paste to create it, if you want. To do this, select the attribute in the main window and choose copy. Then, select the MeasurementType entity and choose paste.
7. We're going to create two new attributes for the MeasuredData entity. The first one is to store a timestamp for each new piece of data that is entered. Create a new attribute, set its name to timeTaken, the column to TIME_TAKEN, the external type to datetime, and the internal type to Date.
8. Next, add an attribute to the MeasuredData entity to store the actual measured data. Since we do not know the type of the data for each row in the database ahead of time, we will set the data column to store a large string. Most other data types can be converted to a string. Set the name of the new attribute to data, the table name to DATA, the external type to char, and the internal data type to String, with an external width of 1024
9. Double-check that every attribute except the ones storing ID numbers have the diamond visible and do not have a key icon. The measured data table will look like this:

Configuring Relationships

1. Copy the dataTypeId attribute from the DataType entity and paste it into the MeasurementType entity. This will allow the measurement type to store a link to each row's data type. Set the attribute so that neither the key or diamond icons are showing (because it is not a primary key, and because this attribute will not normally visible to users).
2. Select the dataTypeId that you just pasted into the MeasurementType entity. In the Inspector, view the Advanced Options for this attribute by clicking the second icon in the toolbar of the Inspector. There should be a checkbox to set whether or not to allow null. Make sure that it is checked so that null values will be allowed.

3. Copy the measurementTypeId attribute from the MeasurementType entity and paste it into the MeasuredData table. This allows the measured data to store a link to its respective measurement type. Set the attribute so that neither the key or diamond icons are showing.
4. Set the newly pasted attribute to allow a null value (see step 2).
5. Select the MeasurementType table and choose Add Relationship from the Property menu.
6. Using the Inspector, name the relationship dataType. Select the DataType table in the Entity selector. Then make sure that To One is selected, since there will be one data type for each measurement type. Finally select "dataTypeId" in both of the Join selectors and click connect. This is what it should look like:

7. Select the MeasuredData table and choose "Add Relationship" from the Property menu.
8. Using the Inspector, name the relationship measurementType. Select the MeasurementType table in the Entity selector. Make sure that To One is selected, and then select measurementTypeId in both of the Join selectors and click connect.
9. Save your EOModel.

Your database schema is complete. To view it in a nice graphical form, use the icon on the far left of the toolbar to toggle to the table view. Your model should look something like this:

Create the Database

Once our EOModel has been created, it's no problem to create database tables that match the design. EOModeler can create the SQL code for you, and will even apply it to your database using the adaptor information that you specified earlier.

1. Select the Weather entity from the left frame of EOModeler.
2. Generate SQL in the Property menu.
3. EOModeler will create all the SQL code necessary to set up your database.
4. Uncheck Drop Tables and Drop Primary Key Support, since there is nothing in the database to drop.

5. Click Execute SQL to apply the changes to the database.
6. If you click the Tables... button and get a list of your three tables, then everything is good to go. If you get an error message, double-check all the model settings. For me, I often seem to forget to assign table names to attributes, so you might want to check that.
7. When the tables have been created, save your work and quit EOModeler.

Create and Use an Administration Tool (Direct to Web)

If you have completed all of the steps above, you've created a simple, but flexible database for holding weather measurements. Of course, it is not very exciting in its current state. Our database does not even have any data yet! Fortunately there is a cool technology in WebObjects that can automatically build a web application, based on our EOModel, that we can use to populate the database. We will build much better data entry tools in future articles, but this will give us some data to play with as we design the rest of the system.

Create the Administration Tool

1. Launch Xcode (you can find it in /Developer/Applications/).
2. Choose New Project from the File menu.
3. You will be presented with many types of projects that you can build. Select Direct to Web Application from the WebObjects group. Then, click Next.
4. Name the project WeatherHub and click Next.
5. Leave the J2EE options set to their defaults and click Next.
6. Leave the Web Service Support options set to their defaults and click Next.
7. On the EOAdaptor screen, make sure that the JDBC framework is selected, and then click Next.
8. Leave the Choose Frameworks options set to their defaults, click Next.
9. This is the important step, so pay attention! Click the Add button and select the EOModel that you created earlier (called Weather.eomodeld). Then click Next.
10. Choose the WebObjects Look so that your project will look like my screenshots. Click Next.
11. The last page lets us choose to build and run the project right away. Make sure the box is checked and click Finish! After a minute or two of compiling, your application will automatically launch and open in your web browser. Congratulations on creating an Administration tool!

Add Sample Data to the Database

Add Data Types:

1. Make sure that you followed the previous steps and that the web application is visible in your browser. You should see a login screen.
2. Leave the text fields blank and click the Login button.
3. On the left side of the window, make sure that the DataType entity is selected, and then click the New button.
4. Type number in the text box and press Save.

5. Repeat steps 3 and 4 and create a data type with the name string.
6. Click the search button on the left side of the screen, followed by the SearchDB button in the middle of the screen. A list of your newly created data types should appear.

Add Measurement Types:

1. Select the entity MeasurementType in the pop-up menu on the left side of the browser window. Click the New button.
2. Assign the name Temperature to the new measurement type.
3. Click the hand icon next to Data Type, and then click SearchDB at the bottom of the window.
4. Click the Add button that is next to the data type "number." Click Return to finish editing the new measurement type.
5. Click the Save button.
6. Repeat steps 1 to 5 to add a measurement type of Wind Speed (just like Temperature, its data type is also "number").

Add Measured Data:

1. Adding some measurement data should be almost the same as adding the type data, so I'm going to leave the steps up to you. Remember to select MeasuredData as an entity before trying to add new data.
2. Add a temperature of 70 for May 1, 2004.
3. Add a wind speed of 5 for May 1, 2004.
4. When you are done adding data, view the data by performing a search in the Administration Tool. You may also want to view the data using the Browse database feature in OpenBase.

Final Thoughts

Congratulations on designing and building a flexible database. We also built an administration tool using WebObjects and entered some data into our new database. Not bad for our first tutorial together! Give yourself a pat on the back if you made it all of the way through. For each article in this series, I will assign some extra credit for those who want to go beyond the tutorial. For this article, it would be nice to have a location associated with each measurement. For extra credit, add another table that stores location data (such as latitude, longitude, and altitude) and create a relationship so that it is connected to the measured data table.

In the next article we'll design an XML format that can be used to transfer all of the data in database. After we do that, we will have our hub and spokes well-defined and we can get into the really fun stuff. Until then, happy programming.

Resources

• Apple Developer Connection
• Apple: WebObjects
• OpenBase

Adam Behringer works as the CEO of Bee Documents, which strives to help legal and medical firms efficiently manage their documents.

Return to the Mac DevCenter

Copyright © 2009 O'Reilly Media, Inc.