Hello, all.

I'm working on a database to inventory trees and various other assets (light poles, park benches, etc) in our neighborhood. I've finally settled on what I think is a pretty solid conceptual design using subtypes and supertypes. Now, of course, comes implementation. I've read about the various ways to implement subtypes and supertypes (supertype only, subtypes only, other some combination of both), but I haven't really seen anything discussing _when_ each technique is appropriate.

For whatever reason, I've gotten the impression that implementing at the supertype level is preferable, since everything is contained in one place (resulting in less tables and easier/quicker data access) and is, ostensibly, simpler. But in implementing my logical model, I'm finding myself having to write some complicated triggers to enforce data integrity (e.g., ensuring that various plant attributes are either filled in or null depending on the asset type, or ensuring that Asset Condition correctly describes the condition of either a plant or a structure). And after reviewing this article, I'm starting to second-guess my decision. Maybe "simple" is actually hard?

At a certain level, I realize the answer is "do whatever works for you." But I'm hoping someone with experience might be able to recommend some guidelines, describe their own practices, or specifically comment on my design here.

Thanks.

BTW, I realize the sample diagrams I've provided are an unholy mash-up of UML and ERD, but it's the best I could do with my diagramming software. Hopefully they're not too strange to look at.