You're not reinventing the wheel, you have a road with a weird surface and you're trying to figure out what rolls on it. There isn't a "one size fits all" solution: your case where a complete entity record is constrained in ways that incomplete records are not is common but not universal. In other systems, there might be well-known default values; or the entity's life cycle could simply hinge on how the values in a few always-defined fields mutate.
Separating storage is only one way to tackle your particular problem. You could, for example, break down your entity into a core with identical constraints at every stage of its life cycle, add subsidiary data to dependent ("specialization") tables as they come in, and use check constraints and triggers to enforce integrity. It's probably not worth the effort unless you have to do some pretty complicated stuff at multiple points in that life cycle, but it's an option -- and sometimes you do have the need for it!
Thanks for the detailed response!
I'll look at the option you proposed here.
Don't look too hard -- it probably isn't worth the effort if you only have a couple of states and don't want to do too much assembly of completed entities! I was just using it as an example to show that there are multiple ways to approach the problem, and choosing the best one is extremely situational.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.