4 ms·

One great thing about not enforcing FKs is that it makes integration testing a lot easier. You can load just the data you need to test with, and none of the FKs

One great thing about not enforcing FKs is that it makes integration testing a lot easier. You can load just the data you need to test with, and none of the FKs need to point to rows that exist that aren't within the testing scope. This approach isn't for everyone. It works well with DDD where aggregates form contextual table boundaries and is eventually consistent by default.

yellowapple 7y ago
If you ain't testing against the full system state, then I'd be hard-pressed to call that "integration testing". If you're generating the test data from scratch, then it shouldn't be hard to generate the dependent data while you're at it. If you're testing against (anonymized) production data, then it shouldn't be hard to pull the dependent data while you're at it. In either case, you should be validating the integrity of those data relationships as part of the test criteria.
- Ma8ee 7y ago
  It isn’t hard, but often very cumbersome. Oh, so you want to test invoicing, for this Customer, which must have a Delivery Address and an Invoicing Adress which both needs valid Postal Codes. And the Customer must have a Contact person. Then we need the Product, that must consist of at least one Article, and each Article must be connected to the Company that we bought it from, with Addresses and Contact Persons and half a dozen other entities. But we have forgotten that the article also need to be in a Category so we know which Sales tax or VAT that needs to applied, for the State or Country of Sale. Then of course, which Sales Person, belonging to a Sales office, with contact person, addresses etc should get credited for the sale. And we still haven’t been able to actually create the order yet, because we haven’t created the delivery options. I think you get my point. I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test. I’m very grateful for tools like tsqlt that make unit testing possible (by temporarily turning of FKs)
  - yellowapple 7y ago
    That all seems pretty reasonable to me, and darn well should be included when "test[ing] invoicing": - You surely want to make sure invoice creation depends on a valid billing address at the very least, right? If that breaks, you're gonna have a lot of rather irate AR clerks. - You surely want to make sure the Contact is aware of the new invoice on the order, right? In fact, that might very well be part of the Contact's performance metrics, so if that breaks, you're gonna have a lot of rather irate sales/support reps (whatever "Contact" means in this context). - You surely want to make sure your invoices are against valid items, right? And you surely want to make sure that expected revenue correctly ties back to an inventory movement, which in turn ties back to an inventory receipt, which in turn ties back to a paid invoice to a vendor, right? If that chain breaks, you're gonna have a lot of rather irate accountants and financial auditors. - You surely want to make sure you're getting the right sales tax calculations, right? If that breaks, you're gonna have a lot of rather irate accountants, financial auditors, and tax collectors. - You surely want to make sure the Sales Person and Sales Office both get credit for the invoice, right? If that breaks, you're gonna have a lot of rather irate sales reps and managers thereof. So... no, I ain't exactly getting your point, lol. If you're changing invoicing, then all of the dependencies and dependents of invoicing ought to be tested. But more to my point: "very grateful for tools like tsqlt that make unit testing possible" Unit testing != integration testing. If you're unit testing, then sure, turn off foreign keys and practice your quick draw while you cowboy it up. If you're integration testing, then that inherently means testing the whole system as a whole; what you call "cumbersome" I call "the bare minimum of comprehensiveness". "I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test." That's usually pretty easy to script, even with foreign key constraints. It might take you a day, but future days should be able to call upon that same script, saving you quite a bit of time :)
    - Ma8ee 7y ago
      I don't think we disagree about much. My only objection was to > then it shouldn't be hard to generate the dependent data while you're at it which makes in sound like somewhat light work. My point was only that it isn't. And of course we script a lot of this test-data-creation, but then you need different kind of data for different tests, and need to add some flexibility. And after a while, just generating test data becomes somewhat complex in itself. And all of the test-data scripts needs to be maintained and changed whenever the model changes. None of this is done in a breeze.

yellowapple 7y ago

If you ain't testing against the full system state, then I'd be hard-pressed to call that "integration testing". If you're generating the test data from scratch, then it shouldn't be hard to generate the dependent data while you're at it. If you're testing against (anonymized) production data, then it shouldn't be hard to pull the dependent data while you're at it. In either case, you should be validating the integrity of those data relationships as part of the test criteria.

Ma8ee 7y ago
It isn’t hard, but often very cumbersome. Oh, so you want to test invoicing, for this Customer, which must have a Delivery Address and an Invoicing Adress which both needs valid Postal Codes. And the Customer must have a Contact person. Then we need the Product, that must consist of at least one Article, and each Article must be connected to the Company that we bought it from, with Addresses and Contact Persons and half a dozen other entities. But we have forgotten that the article also need to be in a Category so we know which Sales tax or VAT that needs to applied, for the State or Country of Sale. Then of course, which Sales Person, belonging to a Sales office, with contact person, addresses etc should get credited for the sale. And we still haven’t been able to actually create the order yet, because we haven’t created the delivery options. I think you get my point. I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test. I’m very grateful for tools like tsqlt that make unit testing possible (by temporarily turning of FKs)
- yellowapple 7y ago
  That all seems pretty reasonable to me, and darn well should be included when "test[ing] invoicing": - You surely want to make sure invoice creation depends on a valid billing address at the very least, right? If that breaks, you're gonna have a lot of rather irate AR clerks. - You surely want to make sure the Contact is aware of the new invoice on the order, right? In fact, that might very well be part of the Contact's performance metrics, so if that breaks, you're gonna have a lot of rather irate sales/support reps (whatever "Contact" means in this context). - You surely want to make sure your invoices are against valid items, right? And you surely want to make sure that expected revenue correctly ties back to an inventory movement, which in turn ties back to an inventory receipt, which in turn ties back to a paid invoice to a vendor, right? If that chain breaks, you're gonna have a lot of rather irate accountants and financial auditors. - You surely want to make sure you're getting the right sales tax calculations, right? If that breaks, you're gonna have a lot of rather irate accountants, financial auditors, and tax collectors. - You surely want to make sure the Sales Person and Sales Office both get credit for the invoice, right? If that breaks, you're gonna have a lot of rather irate sales reps and managers thereof. So... no, I ain't exactly getting your point, lol. If you're changing invoicing, then all of the dependencies and dependents of invoicing ought to be tested. But more to my point: "very grateful for tools like tsqlt that make unit testing possible" Unit testing != integration testing. If you're unit testing, then sure, turn off foreign keys and practice your quick draw while you cowboy it up. If you're integration testing, then that inherently means testing the whole system as a whole; what you call "cumbersome" I call "the bare minimum of comprehensiveness". "I’ve easily used more than a full day just to get enough data in a naked system to make just the simplest test." That's usually pretty easy to script, even with foreign key constraints. It might take you a day, but future days should be able to call upon that same script, saving you quite a bit of time :)
  - Ma8ee 7y ago
    I don't think we disagree about much. My only objection was to > then it shouldn't be hard to generate the dependent data while you're at it which makes in sound like somewhat light work. My point was only that it isn't. And of course we script a lot of this test-data-creation, but then you need different kind of data for different tests, and need to add some flexibility. And after a while, just generating test data becomes somewhat complex in itself. And all of the test-data scripts needs to be maintained and changed whenever the model changes. None of this is done in a breeze.