The longer I work in the field of information technology and information systems it seems the more I appreciate the natural world. Often on walks with my two dogs I find myself looking up at the trees. Although I cannot identify all of them by scientific name, I enjoy seeing them on my little walks as if they are old friends. During out of town travels, I often find it refreshing to take my eyes and view many of the wonderful trees around me. Wouldn’t it be fun to create an application to store photos of trees along with a little story about each tree and post them for friends to admire?

So that is the idea behind this strange tree story database, perhaps there is already an online application that does this, if so this design below is my own quick rendition of a tree database. In the below schema, I’m using sql server 2008 to diagram this model. As you can see, I am following my own preference to break out the most important entities into their own ‘self sufficient’ tables. Those main entities are Trees, Images, and Story.

Figure 1 Tree Story Database draft showing Tree, Story, image entities with junction linking tables

I prefer to break up the main entities into self sufficient tables because it is easier to add them in any order you wish. For example, let us say you have a story about a tree, it will allow you to upload the story without knowing the tree. People don’t always follow the rules, so allowing people to fill in what they want when they want in whatever order, may appeal to more users than applications that force people to enter data in only one way. Same thing for tree images. If you have a photo of a tree, you can upload the tree image before defining the tree.

The capabilities of the application will allow for storing geographic location and doing proximity searches. Photos of trees, single photos or photos about a particular tree would be supported. Imagine users being able to locate their favorite trees via a smart phone. The location field of the Tree entity is a sql server geography type. The application would support the ability to locate your tree via your mobile phone application or to search for trees that are close to your current location. The TreeIdentity field is a globally unique field capable of uniquely identifying the tree in case we want to catalog our tree data in that manner. The TreeImage field could be a varbinary max column to support small or large images. Likewise, the Story field can be a varchar(max) field, allowing us to tell a long or short story about our favorite tree. The two linking tables, TreeStory_lnk and TreeTreeImage_lnk relate Tree to images and tree to stories.