I hope this can serve as a learning experience for the less seasoned readers or at least a bit of humor for the well seasoned.
Several years ago in a design review a developer was given feedback about a particular table design. The design was fairly simple. It looked something like this:
In the design reivew, the developer was instructed by his manager to modify the design to the following:
The design was modified as instructed.
The impact was that anyone writing a query starting at the top of the structure would have to remember to use 'type = 1' or risk displaying incorrect data. As it turns out even the original implementor frequently forgets about this requirement. Numerous bugs have been and continue to be caused by this design due to frequently missing the need to specify the type when joining to this table.
A fiend and colleague recently linked to my original type = 1 post which got me thinking about a potential solution for the problem.
A fairly simple and hopefully not too painful solution would be to create a SQL view for each type.
For example, MyTable2Parent and MyTable2Child. The view would contain the type specific qualifier and allow developers writing other queries to explicitly join to the desired entity.
This change would make the SQL statements easier to read as well.