One of the challenges for many database developers is coming up with a good design that not only meets the specifications but also performs well in queries. Relational databases often seek some level of normalization, which reduces the update load for a system but might require more effort work from developers to write queries. Many modern database developers haven't studied modeling, and often aren't sure how to design a section of a database. This results in some poorly designed entities that don't perform well, might use lots of space, and become difficult to evolve over time.

In this article, I'm going to present a specification, which will admittedly, not be as complete as I'd like as a data modeler. It's the type of situation I've run into many times, and I'm going to ask you to design a set of entities that meet this goal, which is for tracking travels of customers. More details below, but add your entries as comments, ask questions, and I'll try to give you some additional guidance, but as in the real world, customers don't always know what they want.

If I update the spec, I'll add an UPDATE: message in this article.

This is part of a series that asks you to design entities around some common situations you may run into. We'll ask readers to vote on designs they like as well and we'll feature the highest voted one in a follow-up article.

Tracking Travels

I started doing this recently as I was interested in how I'm moving around the world. I'm a geek, and I like data, so I wrote about my efforts with an XLS and Power BI. I started to put this in a database and share a design, but thought I'd rather ask you how you might do this. Here is the specification.

The Idea

There might be lots of people who'd like to track their travels. Maybe to create a visualization, maybe just for fun, maybe just to answer those online viral quizzes that ask "how many countries have you visited?" We want to be able to provide a service where someone can log in and enter their travels or even import them from various sources. We want to store this data and then present it back to users in various ways. They might even want to share trips with others or link them with their friends. They might even want to compare their travels with others.

Our customers will often travel by various means, perhaps plane, train, or automobile. They'll arrive in various destinations, which could be of many types. They will want to know when they've been traveling and where they went. They will need easy to understand names, not complex acronyms. They also want to worry about days, not minute by minute geo tracking, especially as some of them might not want to use GPS-enabled devices that track them. They may input or load this data later.

We plan to have application developers who will deal with imports and exports, queries, and reports. What we need is a good way of storing this data in a database. For now, let's assume SQL Server.

Important Data

I know we'll have some sort of user table with logins and credentials and links to various online services. Let's assume there is a UserID of some sort that is used to track who is logged in and authenticated by some mechanism. What we want to track are these types of data:

• where someone is
• how they got there
• what area are they in
• how far have they traveled
• where did they stay
• links to reviews of the location or method of transport
• links to pictures or other posts
• descriptions of the links
• who was with them that might use this service
• categories of why they traveled
• indicators of whether pictures of others should be blurred out
• anything else

Imagine this is some sort of competitor for TripIt or maybe TripIt with some social networking possibilities. We all know that ideas often morph or change and that what we build might pivot to some other, more profitable, venture.

Think about the questions you might ask, the way you would store this subset of data, maybe the reference/lookup tables you'd need. Perhaps the data types? Ask questions, but remember, business people aren't great with answers. Imagine I'm your business client.

Give Us a Design

Use the comments feature below to give us a design or ask a question. DDL is fine, images of ER diagrams work, but explain why you did something. This isn't that complex, but it could be a fun exercise as downtime between work tasks, some individual learning, or even a brainstorming event at home. Feel free to leave a note (with their user ID) for designs you like or don't like.

This doesn't have to be a long drawn out, time consuming effort, and most of us can guess that something like TravelDate means the date on which someone traveled. If you have a clever idea, please explain it and if you extend this in anticipation of something that I, err the client, didn't think of, let us know.

I'll keep an eye on the discussion to respond to comments and I'll review this in 2-3 week and write a follow-up that picks one of the designs. As a minor incentive, I'll send USD$100 through Amazon to whoever gets the best design. Voted by others with my final decision.

Good luck and have some fun with this.