3 ms·

One thing that saved my bacon is to write scripts that test production data is valid (for the few things that cannot be enforced by the DB, at least not easily)

by Fradow 9y ago

One thing that saved my bacon is to write scripts that test production data is valid (for the few things that cannot be enforced by the DB, at least not easily). Invariably, those scripts found invalid data, which pointed to hard to spot bugs. Catching those early saved me from having huge amounts of invalid data to manually sort through.

hnarn 9y ago
Do you want to give a quick example?
- Fradow 9y ago
  Sure, let's say you have an organization, in which you have users and devices, so users and devices have a nullable FK to organization. Also, a device can be linked to a user, so a device has a nullable FK to a user. Now, when a device is linked to a user, both should be on the same organization, right? I don't see how to easily do that as a constraint (but I have to admit my SQL skills is quite low, and I avoid bypassing the ORM). Well, in production, devices might land somewhere else, and now you have a device that is supposed to be on organization A which is linked to a user on organization B, if you didn't force the organization when a device is linked to a user. Depending on your use case, it could be expected, an operational error, or something that should be resolved automatically. Edit: another example. We have a legacy system. Part of the data is supposed to be mirrored on both new and legacy system, via API calls (I don't know of a better way, I'm not a great dev, DB are different, not shared and on a different provider). Well, check if they are actually the same from time to time (at least the obvious stuff).
  - hnarn 9y ago
    >Now, when a device is linked to a user, both should be on the same organization, right? I don't see how to easily do that as a constraint I'm not exactly an expert when it comes to SQL either, but in this case wouldn't it be better if the "Device" table had two columns: "UserOwner" and "OrgOwner" which in turn have FK connections to "Users" and "Organizations". If the logic is that every device is only owned by a user or an org (never both) at any time, you could constrain: check(UserOwner is null or OrgOwner is null).
dawnerd 9y ago
Thats a great idea, run into a more than a few cases where production data is all sorts of messed up only after running migrations against it.

by Fradow 9y ago

hnarn 9y ago

Do you want to give a quick example?

Fradow 9y ago
Sure, let's say you have an organization, in which you have users and devices, so users and devices have a nullable FK to organization. Also, a device can be linked to a user, so a device has a nullable FK to a user. Now, when a device is linked to a user, both should be on the same organization, right? I don't see how to easily do that as a constraint (but I have to admit my SQL skills is quite low, and I avoid bypassing the ORM). Well, in production, devices might land somewhere else, and now you have a device that is supposed to be on organization A which is linked to a user on organization B, if you didn't force the organization when a device is linked to a user. Depending on your use case, it could be expected, an operational error, or something that should be resolved automatically. Edit: another example. We have a legacy system. Part of the data is supposed to be mirrored on both new and legacy system, via API calls (I don't know of a better way, I'm not a great dev, DB are different, not shared and on a different provider). Well, check if they are actually the same from time to time (at least the obvious stuff).
- hnarn 9y ago
  >Now, when a device is linked to a user, both should be on the same organization, right? I don't see how to easily do that as a constraint I'm not exactly an expert when it comes to SQL either, but in this case wouldn't it be better if the "Device" table had two columns: "UserOwner" and "OrgOwner" which in turn have FK connections to "Users" and "Organizations". If the logic is that every device is only owned by a user or an org (never both) at any time, you could constrain: check(UserOwner is null or OrgOwner is null).

dawnerd 9y ago

Thats a great idea, run into a more than a few cases where production data is all sorts of messed up only after running migrations against it.