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, how they paid, the date a tax letter was sent, etc. But for certain contribution types we need additional information, such as if they bought an Auction Item, sponsored an Event, and so on. Thus, we set up a "base table" of Contributions and sub-tables for the different types, and set up a constraint to ensure that the right subtable is used depending on the type.
It sounds complicated and like it may require fancy triggers or at the very least CHECK constraints, but it is very easy to model in T-SQL -- especially in SQL Server 2005+. The end result is that you have a less redundant database design and much less code to write to maintain that data.
Check it out if it sounds interesting, or helpful, or if none of this makes any sense and you're wondering what the heck I'm talking about.