I need your help. I know how I create databases, and I’ve watched a lot of other data professionals follow their own processes for that, but I want to know how YOU do it.
I’ve written about the process I follow for a complete database design on InformIT (use the "Next" button at the bottom of these to see them all). Beyond starting with the business requirements and REALLY hammering that out, here is the general outline I use:
1. Pull out the nouns from the business requirements (“Client”, “FirstName”, “LastName”, “Business Name”, “Business Street” )
2. Group the nouns into “parent | child” sets (“Client: First Name, Last Name”, “Business: Business Street”)
3. Continue Steps 1 and 2 until you can’t do it any further. These are your tables and columns.
4. Set a value that uniquely identifies every record (line). This becomes the Primary Key. I normally use a “surrogate” key, but a natural key also works if you need it. I don’t like compound keys, but I’ll use them where they make sense. But the overall goal is that every key should be able to identify one “Buck Woody” from another “Buck Woody” record. If it doesn’t, I haven’t done steps 1-2 enough, or perhaps I’ve done it too much.
5. Examine each and every column and ensure that they are in the proper type. Check the business requirements to make sure. (dates are always dates, money is money, numbers are always numbers and so on) Repeat until everyone agrees.
6. Examine the relationships between the tables. Can there be many clients in a company? Can there be many companies for each client? This sets up Foreign Keys, and potentially other tables to solve many-to-many relationships.
There’s a bit more to it than that, and the business requirements side of things are where I actually spend the most time. If you get that wrong, the most beautiful design in the world won’t work over time.
I currently use DBDesignerFork for my design documents to coordinate with my business and development teams, which is not a perfect tool. But Microsoft doesn’t have a good one (we did in Visio, but we messed that up) and so this is what I have to work with. I don’t keep up with this diagram after I create it; it’s just a tool to help me communicate from business to dev to DBA’s.
So now here is where I need your help. Will you post a response here (if you design databases very often) that tells me:
1. What process do you follow?
2. How important are the business requirements?
3. What tool do you use to create the design, do you need it to diagram, do you even care about diagrams?
4. What’s your biggest pain-point about designing? Not with SQL Server, mind you, just the designing part.
1) Understand the requirements by asking questions and making suggestions.
2) Get a basic idea of the objects, their attributes and their relationships.
3) Identify a model that makes sense for the project.
4) Grow the model to fit the actual requirements.
These seems to happen all at the same time. Perhaps there actually is some order.
I follow this process with a couple of additional details.
2/3. I strive for a third normal logical model and only de-normalize when it is proven that performance can be improved.
4. I believe that it is important to always identify the natural key. I almost always use a surrogate key for the primary key but also create a unique key for the natural key. This makes it difficult to enter duplicate data and easier to locate a record if you know the natural key (Buck Woody) but have no clue about the id (some arbitrary 10 digit number). This is also necessary if you are normalizing your database.
6. Relationships are important and I usually determine them as part of steps 2. and 3. I like to review, with the customer, the relationships I have determined from the requirements to make sure they are always true. If the customer says it is true sometimes, there is something I do not understand and the relationship needs to be revisited to determine if the model needs to change.
Check out a lot more dialog for this post on SQLblog: