Last week Buck Woody asked how we all design our databases, so here’s my answers to his questions and a little about my process.
I’ll get to #1 in a second.
#2 How important are the business requirements? They are probably the most important thing to me when doing a database design. If I don’t have the requirements down pat, or I don’t understand the requirements then I can’t put together a proper database design.
#3 What tool do you use to create the design, do you need it to diagram, do you even care about diagrams? I usually don’t care about the diagram to much. I can see that in my head when I’m coming up with it. I usually go directly into T/SQL creating tables after I’ve worked in through in my head. For a very complex database I’ll bust out Visio and use that.
#4 What’s your biggest pain-point about designing? That’s easy, the business giving me a moving target to hit. Sometimes the moving target doesn’t impact the database design, sometimes it does.
#1 What process do you follow?
I take the business requirements are translate them from useless marketing speak into something usable, then go back to the business and see if that’s what the actually want. After doing this three or four times I now have a usable set of requirements that I can work off of. I’ll take these requirements and use then to decide what needs to be stored where, and how it fits into the rest of the environment (we are an application developer so everything goes into our production database, or our shopping cart database).
From there I work with the .NET developers to determine how they will need to see/use the settings which will be saved for our WebUI and the .NET services which run in the background. Then I work with the C++ developers to see how the client which we deploy will need to see the data. From there I’m able to get a good idea of how the data needs to be stored.
Sometimes it is simply easier to store the data as an XML blob because the database doesn’t need to see or use the data, it is simply storing the data so an XML blob then becomes the most flexible way to store the information.
When ever possible I’ll use the natural key of the database table as the primary key. I’m not fond of identity values for the sake of having an identity column on a table. If the column won’t actually be used for anything there is no point in having it there.
Because of the nature of our system we usually end up with LOTs of composite keys in the database as we to many to many relationships between different tables to use as settings.
So that’s why basic process that I use. What do you use?
Denny
