Database Design
Database design and modeling.
A Simple Single-Child Model
Consider the following logical data model:
There are multiple Companies
Each Company has many Projects
Each Project has many Tasks
Each Task has a Status, selected from a global list of pre-defined Statuses.
Status Companies
\ |
\ |
\ Projects
\ |
\ |
Tasks
Let us say that we decide that the primary key of Companies, Projects, Tasks, and Status are all Identity (auto-number) columns, since we would like to auto-generate primary keys for those tables.
So, we have something like...
Most people know about cascading updates and deletes, but did you know there are two other foreign key constraint options you can use to maintain referential integrity?
Read all about them in my latest article over at SQLTeam.com.
These features, introduced with SQL Server 2005, haven't got a lot of publicity, but they can be very useful. I just used the SET NULL option recently for the first time (inspiring me to put together an article on it) and it works great.
Introduction
There is a very simple rule when it comes to storing (and returning) data, which I see violated all the time, making life so much more complicated for everyone involved. In case you haven't noticed, that's a common theme I discuss here on this blog -- different ways programmers make life more difficult for themselves, instead of simply following good practices and doing things the easy way. This is yet another example of that situation.
The "Golden Rule of Data Manipulation" is a simple, but important rule that you should always follow when designing a database, writing database code, or really...
Ah, this is not an anti-identity rant, don't worry!
Though, in a round-a-bout sort of way, it is yet another argument against always blindly using them -- but not in the way you might expect.
There is a simple rule I'd like to propose, let me know what you think:
"If your client code, SQL code, or configuration files reference the primary key column of a table to determine any application logic, that primary key column should not be an identity."
Now, I am not saying that primary key column can't be a meaningless integer. I am just saying it should not be an...
I have a new article up at SQLTeam:
Implementing Table Inheritance with Sql Server
It discusses the situation where you have multiple entities that are distinct, yet they have many attributes or relations in common. There is an easy way to simplify your database design and your code if you use the concept of a "base table" for that common data, which is very similar to the concept of Inheritance in Object-Oriented programming.
I recently had to do this for a client that tracks Contributions to their foundation; there is a base set of data that all Contributions have, such as who donated,...
Let's say you have a database that contains Companies, Products and Stores. Products and Stores are unique to each company -- i.e., they are not "shared" across companies. read more...
The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. read more...
Ah … primary keys … such a topic! When discussing what columns to define as a primary key in your data models, two large points always tend to surface:
Surrogate Keys versus Natural Keys
Normalization
These can be very complicated and sometimes polarizing things to debate. As I often try to do, I will attempt to approach this topic from a slightly different perspective.
Let's start things off with what I feel is a good interview question:
How would you define what a primary key of a table is?
a. An auto-generated numeric or GUID column in the...
Dealing with poorly designed databases is a simple and common fact of life for programmers. It happens, sometimes due to lack of experience or education, or sometimes because business requirements were never analyzed properly or they changed. It's hard to avoid poor database designs, but it takes only a simple concept to make fixing those designs much easier. read more...
What is data, and what is code? How do we define the difference, and decide what goes where? It is great to say "keep data out of your code", but what if that data is integral to the application itself? Isn't it therefore code, and not data? read more...
I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...
You're INSERTing multiple rows from an un-normalized import table. Each row is assigned an identity primary key. You know you can use scope_identity() to retrieve one identity at a time, but how do you retrieve a whole set of them? Are cursors the only answer? As usual, it depends on having logical specifications and a good design. read more..
SQL is a set-based language, and often we wish to pass sets of data as a parameter to a stored procedure. For example, you might wish to pass a set of Customers to an invoice generating stored procedure, or a set of employees for which you’d like to calculate a particular bonus. SQL Server already has everything you need to do this, and you don't need CSV or XML strings. read more...
There’s a handy little rule of thumb I use when developing my database models. It comes up often in situations like this: If we are storing phone numbers in the database, should AreaCode be a “free-form” char(3) column? Or should it be a foreign key reference to an AreaCodes table? read more...
I often talk about "database layer" versus "presentation layer", but even the within just the database layer it is important to understand that how the data is physically stored does not always have to correlate with how the database returns results. read more...
Be sure to visit SQLTeam.com to check out my latest article, Custom Auto-Generated Sequences in SQL Server. It addresses a common question we see in the SQL Team forums.
Everything you ever wanted to know about how to use SQL Server's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL!
read more...
It's been a while, so let's open up the old mailbag. Today's question is about writing a character-based check constraint. The answer: Simple ... do the *opposite* !
read more..
Today's article at WorseThanFailure.com is one that the SQL Server community might find enjoyable.
I may try to implement something similar in my next project .... or, maybe not!
Are there another other great designs like this that you've come across? Maybe even some that you designed yourself? Come on, you can admit it, let us know! I won't tell anyone. After all, no one ever reads the comments ...
see also:
Discussing VB and/or MS Access in a Programming Forum
Top 10 Things I Hate About SQL Server
SQLTeam Dating Advice !
...
Dear DBA --
Thanks so much for helping us developers out with the latest changes you've made to the stored procedures in our system. While it may have been nice if we got together first to discuss these changes, I do appreciate that you worked very hard to make things much easier for us.
read more...
Here’s my advice of the day: Try your best to store all of the data relating to your database in the tables.
Sounds pretty obvious, I know, but you might be surprised just how much data in your application is not in your tables, where it belongs, but rather nestled away in your code.
read more..
Here's kind of a funny/scary thread over at the DailyWTF:
read more...
Here's a scenario many of us have encountered: You inherit a database. TableX has an identity column "ID" set up as the primary key, and contains many duplicates.
read more...