Some Examples Of Testing Tables And Views In Database

In our project testers are using database (DB) all the time. We query/modify/delete data in test databases to check data flows, to configure something or to just make testing faster (sometimes it's way faster to change some value in DB than to change it through application).

That means, that testers can also test the DB objects – tables and views.

Usually I test them after creation – if I have to test some new component in which the table or view has been added/modified, then I always test this table or view. And in this post I write about some common but not trivial problems that I see at work.

Popular question: should I test DB object, if .sql file of creation was made by analyst?
Definitely yes, you should. Generally analysts don't know much about DB rules and they may not see what problems may create "smelly" structure. Not to mention the fact, that you should always test not only developers, but analysts (and everyone!).

How can I test tables?
Here are some concrete tips that I haven't seen in other articles.

• If there is field like STATUS and field like IS_DELETED find out what's the difference between them (if you want to delete row should you set STATUS=DELETED or IS_DELETED=Y)? Often they are used for the same purpose and you actually don't need one of them. If you keep both and they do the same thing you will end up to change always both values, which is double work.
• If there is some value with foreign key its type and size should be the same as primary key to which it refers. Example:
  table USER has PK column NAME(VARCHAR2 55)
  table ACCOUNT has FK column USER_NAME(VARCHAR2 50)type of ACCOUNT.USER_NAME is wrong, because it's shorter than the column which it refers to. So you can insert some long username into USER table, but you won't be able to create account for that, because ACCOUNT table can't save that long username.
• Column type and size should match the data. For example, it seems like column for the telephone number should be the NUMBER, but actually people want to write their telephone numbers with spaces and dashes, so the column type should be rather VARCHAR (you should also check does appropriate application field has necessary constraints – you shouldn't allow to users insert symbols into field that saves data into NUMBER column).
• NULLABLE property should match the logic. If some field is not mandatory in the application, then appropriate column should not be NOT NULL.
• Almost all tables have (or should have) primary or foreign keys, so you should understand them. If some column refers to the data from another column – it should be the FK for sure.
• Table and column names should be logical and clear without documentation. This may seem obvious but a lot of testers are afraid to ask to change the names, because they thing that it's not important (we already have bad name, ok then, nobody wants to change it, lets move on). But actually it is important, because good names saves your time in the future when you need to come back to this table and can't remember what was this bad name for.
• Learn about database normalization. There are some rules in organizing tables and fields to minimize redundancy.

How can I test views?
Testing view means basically testing the SQL query what creates the view. So you need to know SQL to test does query return all necessary data and doesn't return unnecessary.

• Double rows with identical data is common problem in queries with OUTER JOIN conditions. View shouldn't return double rows even if it seems harmless (you never know how you gonna use this view next month, maybe you need to count all rows).
• You need to understand difference between OUTER and INNER JOIN. OUTER JOIN may return more data than you need, INNER JOIN is faster.
• Usually you don't want to see cancelled or deleted rows in view. Analyst may not write that condition in specs, because he thinks it's too obvious and developer may not write this condition, because it wasn't mentioned in specs. So be sure you understand the purpose of view, to decide should it return cancelled rows or not.
• You may have some table with properties or parameters in your DB and view can take some parameters from this table. In that case it's possible that in test DB you have (and always had) only one parameter, but in live – two or more. Developers often don't have permissions of live DB, so they don't know the real data. Be sure that query can work with multiple rows in this parameter's table (maybe developer used = instead of IN).
• Be sure that data in the view is logically correct. For example, I had once one view with two columns in it: DOES_DOCUMENT_EXIST and DURATION_OF_DOCUMENT. And I found some rows where DOES_DOCUMENT_EXISTS was N, but DURATION_OF_DOCUMENT > 0 – obviously if document doesn't exist duration should be 0. In that case error can be in raw data in tables, not in view (then you should find out why data is wrong).
• If queried table has ID column which you don't need in view – keep it anyway. ID is the easiest way to find row in the table, so keep this possibility.
• Not exactly the DB thing, but some field in application may take value from view. You may check does size and type of returning value match the size and type of field. For example, what happens if you return value with maximum possible length? Or may be your field takes values from two columns (sums two texts, for example)? What happens if both texts are with maximum size?
• SQL query should be easy to read, so if it's complex query be sure that every table has unique and clear alias, columns are in right order (for example, START_DATE is right before END_DATE or ID is at the beginning), column names are unique (if query returns two ID columns from different tables be sure the name of each column is changed to something like USER_ID and ACCOUNT_ID) and the whole query is formatted. Even if you understand the complex query now, you may forget about it in a year, so make it as easy as possible to save the time on understanding.
• NB! Dangerous step, if you don't understand how view is working don't do that! Run query of the view in live DB. It may identify performance problems (live DB usually contains more data than test DB) and in DB testing it is always good to work with real data.

If you also support customers, then probably you are the person who uses DB more ofter than others. Which means you are the person who should care about DB structure and keep it clear and tidy.