On February 26, I will be doing my "How to Design a Relational Database" pre-conference session in Tampa, FL. You can see the abstract here if you are interested, and you can sign up there too, naturally. Currently priced at 158.19 it is a bargain and I totally promise it will be around 8 hours of talking about and designing databases (including lunch and break time), which will certainly be better than what you do on a normal work day, even a Friday.
You can read the abstract, but what should you really expect? First off, expect to not sit in one spot while I drone on and on for 7 hours of lecture. Honestly preparing to talk for 7 hours straight would actually be easier to prepare for, but the best part of the class time is doing some database design. This means I need requirements that will touch on some of the more interesting design patterns and be achievable enough in around 45 minutes that we can all learn something from the practical experience of doing a design.
The day is broken up into 3 or 4 modules (depending on where the class wants to go), with several participation exercises along the way. The sections are:
Section 1: Introduction with a bit of history
We start with just enough introduction to the materials, the stuff you need to do *before* you design, and introduction to the history of the craft to make sure we are all on the same page. A big part of this section is just getting it straight why we design like we do and establishing some of the terminology, and understanding the process of designing and testing a database.
Section 2: Modeling and structures
This section will cover the fundamental building blocks of relational databases, like tables, columns, keys, etc; and how to create a data model of the constructs. This is by far the largest part of the lecture, and by the end we should all be on the same page as to what goes into the database, if not exactly "how" the final product should look.
We will stop at this point, and I will get out my modeling camera (which sounds a LOT more glamorous than it will turn out to be) and we will do some modeling on paper, eliciting attendees to provide the parts of a simple database, and we will all decide what should go into the model.
The document that I base this model on is VERY simple, but the complexities of translating the document to a even a primative database design are always quite interesting to experience as a class, and I get an idea of who is going to be the outgoing class members at this point too.
Section 3: Model Standardization/Normalization
In this section, we will look at the kinds of things you need to do to the model to prepare the model to be implementable by truly analyzing the structures to see if they make "sense" within the confines of the relational model. It is always interesting to me that all models are normalized to some degree, but people think that normalizing makes things slower. In fact, normalization is generally to make business databases faster. The misconceptions about the higher normal forms are always confusing to me as well because the pre-concieved notions that people have are often really wrong. (I think too often the problem is that a properly built database does not lend itself to a simple UI…so you either get messy UIs or messy databases. It is really hard to get a team together that can do both and build the software to make messy business needs into beautiful, easy to use interfaces.
Once we are done with the slide material in this section, we will start a couple of exercises. The first exercise is planned as a full class exercise, where I will man the data model (first on paper, then in a modeling tool), and elicit input from the class, in a manner that make sure everyone gets a say.
Then we will break up into small teams and build a final model on paper, which I will bring up to the projector and we will discuss the different solutions.
Section 4: Physical Modeling Overview
Assuming we still have time/energy (and we don’t have discussion that lasts the rest of the day), we will take the last part of the class and cover turning the model into a "real" database. Data types, domain implementations, constraints, testing, etc. will be covered.
Due to the limitations of the limited time format (in a perfect scenario we might have 3 or 4 days), and a *strong* preference of previous classes towards actually doing some design, there are topics we won't cover. But honestly, if you can get the basic design correct and make the model close to what final model ought to, the rest is kind of gravy (and well documented in a lot more websites and books than mine!) When I am building a database for a customer, I spend a lot more time figuring out the shape of the database objects than I do on the implementation aspects. Almost always you will find the problems with database designs come down to an inability to match the user’s requirements to yhr tables created. Missing an index here and there can be caught in testing and even after implementation (and that is why God created DBAs & Data Programmers).
What I really love about doing all of the designs is that we really get the flavor of a real design meeting. A few differing opinions, a few ideas that I hadn't planned for, and a few argumentative types who really want their own way. But none of the arguments so far have gotten out of hand so far, and they have all been very much like the typical data modeling meeting.
I hope to see you in class, and stay tuned to this blog entry for any more details as they come around. And don’t forget to register for the class: https://www.eventbrite.com/e/sql-saturday-precon-2016-how-to-design-a-relational-database-tickets-21099706791, and definitely don’t miss Saturday’s event http://www.sqlsaturday.com/489/eventhome.aspx. This is my first year to go to Tampa, so I am stoked to see how they do an event!