> Haven't you been 300 hours into the development of a system when suddenly someone springs on you, "Oh yeah, Bob has two bosses. Why should that be a problem?" And you're screaming internally, "Of course it's a problem! You assured me six months ago that everyone only has one supervisor, and now you're telling me this one guy has two!? This changes EVERYTHING!"
I have had this issue and it does change everything -- all the systems that depend on that data, the calculations involved, the user interface. The change in the database schema is actually relatively minor compared to the rest of the code. But with an enforced schema there's a never a case where it will be wrong -- the code with either work all the way one way or the other but never both at the same time.
Neither of examples provided has anything to do with foreign key relationships. If Bob can have two bosses, that's changing a column into a relation. That's going to have an effect beyond the database. If the facilities manager is optional then that's changing a column to be nullable. Again most the work after that change is outside of the database.
Even if drafts or temporary invalid states exist, I'm never going to want orphaned data. You said table definitions are column types are a "level below" business logic but the table definition is directly tied to what you're trying to store and how you're storing it. I think I would simply argue that foreign key definitions are also part of that "level below". It might be that not all facilities need a facility manager but if one is specified it better exist and you better not be able to remove it without affecting the facility.
I have a bug right now where in some race-condition situation the database is catching the issue with the constraints. I haven't found the bug yet (it's low priority) but at least it's not filling my database with invalid data -- it is just crashing that operation.
> I have a bug right now where in some race-condition situation the database is catching the issue with the constraints. I haven't found the bug yet (it's low priority) but at least it's not filling my database with invalid data -- it is just crashing that operation.
This right here. The thing that all proponents for nosqls and "this one trick
will turn sql hell into sql heaven" don't seem to take into account is that a
properly designed schema will save your ass when the front end(s) have bugs;
and yes, properly designing a schema is work, up-front work that needs to be
thought about before writing the first line of code (or sql).
Thanks for posting this, at least I know I'm not the only one that feels
this way.
I have had this issue and it does change everything -- all the systems that depend on that data, the calculations involved, the user interface. The change in the database schema is actually relatively minor compared to the rest of the code. But with an enforced schema there's a never a case where it will be wrong -- the code with either work all the way one way or the other but never both at the same time.
Neither of examples provided has anything to do with foreign key relationships. If Bob can have two bosses, that's changing a column into a relation. That's going to have an effect beyond the database. If the facilities manager is optional then that's changing a column to be nullable. Again most the work after that change is outside of the database.
Even if drafts or temporary invalid states exist, I'm never going to want orphaned data. You said table definitions are column types are a "level below" business logic but the table definition is directly tied to what you're trying to store and how you're storing it. I think I would simply argue that foreign key definitions are also part of that "level below". It might be that not all facilities need a facility manager but if one is specified it better exist and you better not be able to remove it without affecting the facility.
I have a bug right now where in some race-condition situation the database is catching the issue with the constraints. I haven't found the bug yet (it's low priority) but at least it's not filling my database with invalid data -- it is just crashing that operation.