Testing input fields based on the object’s data type in the database isn’t testing I hear discussed. But each data type has its own boundaries. Each major database vendor maintains different data types and data types change with database versions.
Anything with this much variety and versioning is likely to have bugs.
And while this form of testing might not seem exciting, if you can find a referential integrity bug, you’ve got something good.
If I work on an application for an extended period of time, I usually memorize the core tables in the database. I study the data model and look over the data types. I look through the database to find the fast growing tables (to help me think through different aspects of performance testing – but that’s a topic for another time and I digress) and specific data types I’ve come to learn that are more likely to cause issues.
If you want to find more complex bugs than what a user might find, one method is to learn the underlying structure of an application and challenge the application by knowing something about the data types and the data model behind the screens.
I’ve found bugs with BLOBS when I try to do an assortment of operations with a blob. Unit testing will often check the creation of an object stored as a blob but other operations I can do with a blob like copy, export or delete an object that’s stored as a blob in the database can be a good test. There was a time that I would scan any data model I worked with specifically looking for large data types such as Blobs or Texts. In basic terms, big fields often cause trouble.
DATES (this is MySQL specific). If you test data replication the datestamp can be essential because this is often the flag used to determine most recent value on a field. (I’ve spent lots of testing data replication in different applications). If time isn’t being calculated down to the millisecond then try pounding in data faster than the precision being used. Think about time precision when time is being changed – daylight savings, timestamps from users in different locations. It’s a bit like finding a rounding bug in numeric fields – you need to hang around the fringe to expose the issue.
ENUMS, the data type often used for drop down fields, bring to mind other test ideas. If I can author new values for a drop down, how do the values get pushed out to the user? Can I find a caching bug? What if I use the value in a record – and then delete the drop down (ENUM) value, can I find a referential integrity bug?
So much data so little time. I often locate one of each of these data types to test out my ideas. It’s a calculated risk to assume anything coded one way in an application will be coded the same elsewhere in the application (especially with multiple developers) but I think one test with the most vulnerable data types can give me a feel for lurking issues. And one test is better than no tests because I think there’s no time to test any tests based on data types. I find it mind expanding (fun) to test by data types because it forces me to get close to the data model and that from my perspective is time well spent.