As I have been walking around Disney World this week, my mind starts to wander to matters of database design. Sad, perhaps, but I will guess that most people who read this blog do the same much the same thing with whatever technology they are good at when they are relaxing also.  It also may actually have helped me come up with an example for my next book (especially if I can double the size of the book!)

Examples are the hardest of all parts of writing for me. Thinking of an example that covers a lot of situations as is really hard, but I think that a theme park might actually be the answer (if it is and you are a writer, don’t steal my idea or I will steal it right back :).  It seems like it actually covers most if not all of the situations that are needed:

• Good entities (People, places, ideas)
• Rich attributes to describe the entities
• All of the different parts of normalization, including 4nf (show, cast member, location)
• Geography data (to locate all of the different locations)
• Geometry data (finger print biometrics)
• Image data (pictures of attractions, menus, etc)
• Subclasses

And many more. 

But it is the Subclasses that got me thinking. Initially I was thinking that a Location entity would be the top level entity, then attraction, restaurant, shop, etc as subclasses.  But is this really a subclass? Thinking about the AdventureWorks database the other day I realized the reason I dislike that design is the way it has a BusinessEntity entity as a superclass to almost EVERYTHING. A subclassed entity isn’t like a generic object in an object oriented design, but rather it should be more of a specific entity with even more specific information tacked on for specific types.  The point being that you would figure to use the superclass quite often as itself, with the subclass giving you some additional information sometimes, but not as a rule exclusively using the subclass entities.

So how alike is a roller coaster, or a restaurant?  Seems that the only thing these share is a position on the globe. So instead of a subclassed entity, why not specify standalone entities for each and create an independent entity for position.  This would be a geographic point, and would serve as the entrance of sorts to the math of determining how far apart the different locations are, as well as (possibly) to predict which place a person would be likely to go next.  This position is not really a parent to the items, but more of an interface (though technically I don’t think an interface doesn’t specify storage…)

So each table would get a relationship to the position entity that would contain all of the different locations of “stuff” that you have. This is not so different from how address is often implemented, except that an attraction shouldn’t need more than one position. It might have a geometric representation of the area it takes up, but I would think that each location would have one single point on the map that would represent it.  Might this get so detailed as to have multiple items per location?  For example, a very large store having multiple entrance points, each so different as to direct a person in a different way?  Sure, and this might be a matter for the other tables to worry with.  Each position would also need to be able to tell the distance from itself to other positions, with knowledge of the terrain.

Just having an interface would make things easier to use the subclass technique for the things that are common.. Like a queue.  There are different types of queues all around a them park, and several different types. A roller coaster uses several of them itself (a special ticket to get you through fast, a just stand here and wait line, single rider, etc) as does a restaurant (just stand here and wait, and in some cases, reservations, or priority seating).  Each of these queues shares commonality, but at the same time some differences are obvious.  And an actual queue for one ride may use all of the different types.

Clearly this is more to flesh out if I use this example…Just food for thought about subclasses.

As a different example, would it seem natural to have a person entity that was then subclassed into guest and cast member? They do share obvious commonality, and (given the right technology) you would want to track all of their movements. But you probably don’t know that much about guests. Maybe guests aren’t considered people as much as tickets (in the database only) and the ticket is associated with the human characteristics of the guest (height, weight, sex, etc so as to help direct them on their way/predict what they might do? Small kids, fluffy bunny ride?)  Then we might be back to guests and cast members sharing an interface for their location. How alike are location and position?  Probably look alike, but you would want a history of movement if you could track a person’s ever move, right?

The goal of a database is to store the information in a manner that is useful for analysis both to predict future events, as well as to look at how current processes work each day.  Getting everything just right is the dream of every designer. Of course all of this tells me that I have to be careful with this example or it will grow REALLY huge.  Hopefully I won’t need to finish this too soon, but who knows when the next major version of SQL Server is.  Much to consider, there is.