August 2008 Blog Posts
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.
Always remember that the column names returned in a result set do not have to be the same as what you eventually output at your presentation layer.
For example, suppose you have stored procedure that accepts a @CurrentYear parameter and returns a sales variance between the current year and the previous year for each customer. I often see programmers struggling with writing dynamic SQL to produce output like this:
CustomerID 2008 Total 2007 Total Variance
---------- ---------- ---------- --------
ABC $100 $50 $50
DEF $200 $250 -$50
That is, the names of the columns vary based on the data; that is not a good way...