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 a few times, and I'm going to ask you to design a set of entities that meet this goal, which is for students, classes, and registrations. More details are given 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.

Creating a Schedule

Lots of organizations need to handle schedules for various things. We all use Outlook for personal schedules, but schools, conference companies, and plenty of other places need to track schedules. You might even want to use some sort of system like this for project work with people in a development organization. Perhaps you have work that gets assigned to people, or they pick from a list, and you need to determine if someone is overloaded, is everything assigned, etc.

Can you do a better job of building a schema for tracking events, people, and time?

The Idea

Having people register somewhere, get a list of things, and then schedule them is a problem that meets many situations. Certainly the common one is for a school of some sort where students are allowed to pick classes. Another might be a hotel (or other venue) where people can register and rent space. Even SQL Saturday or a conference might want to let users pick from a list of sessions and create a "schedule" of things to see. Maybe they want multiple options.

This is both simple and not simple. Let's imagine that we want to build a registration system that displays classes, which have schedules, and then allows students to pick from these classes and add them to a schedule for an event. The event could be a semester or a day. Be flexible in your design, after all, we might have to pivot this model a bit if we can't sell to a university. Maybe we could sell this system to conferences instead.

Important Data

These are items in the specification that you need to think about:

• We have some sort of way to track people who register. These could be students, but maybe not. Instructors? We might resuse the "people" in different ways.
• We need some contact information for them
• We might need to decide on multiple authentication mechanisms that we support for users
• We will have a list of things to schedule (classes, webinars, etc.)
• Schedulable items might need to repeat or be single events
• Metadata for schedulable items are is needed (description, cost, start, end, repeats, location, etc.)
• A user can schedule items and build  a schedule of some sort for themselves
• There could be limits in how many people can schedule an item

Feel free to treat this as something for a school and name things appropriately, but this could end up being used by a company for their internal tracking of events, training, etc.

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.