I have been working on an open source Museum Collections Management web application that is to be used to keep track of a museum's accessioned, donated, loaned or otherwise acquired artefacts.

This involved designing and creating a rather large database (in relation to my previous experiences), which stores all kinds of varying information (artefact information, changing location information, personal contact information, pictures, etc), that needs to be flexible and easily extendible.

I am just finishing my university degree and I am not a professional when it comes to database design and so I am really wanting to create a thorough test suite to ensure that what I have in place "works".

I have read up on database testing and have come across a few articles that mention Regression Testing in regards to databases but I do not fully understand what this all involves. From reading this article at Dr.Dobbs I understand that one sort of testing that I will need to do is validate that the logic in the database is correct. So I would create tests that insert certain data into the database and then follow it up with a query to ensure that I get the correct data back from the database (ensuring that all the appropriate triggers or views are working).

The confusion comes in with the mention of testing for "Data Quality". In the article above the author makes mention that you want to validate the following with tests:

  • Column domain value rules
  • Column default value rules
  • Value existence rules
  • Row value rules
  • Size rules

What types of tests would this involve and how would they be implemented? Also this is my first time writing a test suite for a database, are there any good guidelines on how/where to begin or any processes that I could follow to guide my test development?

2 Answers 2


A complete answer to this question would be very long. I will try to mention the main points.

To separate concerns, you may be looking at tests to:

A - Validate the database design.

B - Validate that the program(s) are interacting correctly with the database.

The database design validation should be performed by the person(s) who designed the database. The developers (while unit testing) should be concerned more with part (B). The primary difference I see between the two types of tests is the tools used. For (A), you'd use an environment independent of the project code, whereas on (B) you'd of course use the project's code. In the following text, I will mix both.

To answer your specif questions:

Column domain value rules

Each column has an associated data type. Each column must be validated against business rules to prove that it represents the correct data type. Problems may arrise if the column data type is not compatible with business requirements or if the code uses a data type different from how it is defined in the database.

For example:

  • If the column is defined as small int, you should not be able to store text in it. This is an important test specially when the columns are optional, since it could go unnoticed until someone actually enters some data in it.

  • Could you store a negative value in a column where the business requires that to happen?

Column default value rules

Some columns are associated with a default value specification in the DDL (Data Def. Language) where if during insert the insert does not provide a value, the database will assume the default value. This can be tested by not passing the value and observing the result value the database stores. This test may also include checking for Nullable columns. This rarely requires a test since it can be verified from DDL unless an unique index is built on the column.

Value existence rules

As I understand this, you verify that data inserted or updated shows as expected in the database.

Row value rules

I am not clear on what this one means exactly.

Size rules

Each column has a size in the database based on how it is defined in DDL. you want to make sure that any value that fits requirements(either entered form GUI or resulting as an output of a computation) would be stored correctly in the column. For example a Small Integer data type does not allow you to store a value of 5 billions. Also, a name defined as VARCHAR2(30) will not accommodate 40 characters, so business rules must be very clear here, specially when the column is used for aggregating data. You want to test what happens in such situations.

guidelines on how/where to begin

One way of doing this is to come up with a testing plan. You want to ensure you are using the correct version of specifications (such as requirements documents and Use Cases) and of the software. You also need to coordinate thes tests with tests done by Unit Testing (if any). You may find duplicate tests that you don't need to perform again. You want to take a copy of the database before testing so that you could repeat a specific test if you ever needed to. The DBA may help you with this. You also need to check with your team how do they document theses tests and verify the testing scope with them. You can break your database into logical parts and begin the testing of each logical part separately. The testing process could begin by studying the DDL of the database and verifying that the columns are defined correctly as required by the business. You should be using the application's software and not any other tool to perform the majority of the tests. For example question the following:

  • Is the column supposed to be of the defined type (no point in making a Name as Int).

  • Is the size compatible with business requirements?

  • Are all the columns in business requirements found in the database?

  • Are null columns really optional?

  • etc.

Next, you could design test cases to test the above. You could use the GUI to do most of the tests.

There are other important database tests that you have not mentioned. Those deal with:

1 - Testing that the primary keys are really unique from a business perspective.

2 - Testing that unique indexes (other than the PK) are really unique from business perspective.

3 - Testing Foreign Key constraints work.

4 - Testing what happens when a rows is deleted and its effect on related rows.

5 - Other tests regarding special database constructs such as CHEKC, Triggers if they exist.

6 - Correct table normalization and that normalized columns hold correct values.

The above is not a complete list but it should get you started.

  • Thank you for the detail in your answer and your suggestions for test development seem like a good place to start. Thanks for your help.
    – Kristen D.
    Commented Aug 25, 2012 at 6:33
  • KristenD. @Songo I appreciate the feedback.
    – NoChance
    Commented Aug 26, 2012 at 13:41

I think you are approaching this the wrong way.

Any database I know verifies data before inserting it into tables - it validates it against the definition of each column. You can't enter a 80 character string into a SMALLINT(3) column - the database will fail that attempt and let you know you've made an error. You don't need to test for that yourself by inserting the data and then retrieving it.

What you do want to have are validation / filtering rules on data before it is sent to the database.

  • Making sure the data fits the accepted types and range for each column, and filtering unwanted content
  • Making sure to properly escape it to avoid errors (and possible SQL injections if you have a public interface)

Those validation / filtering rules should run on the data in your actual application. You can then setup tests to make sure they are correct, by feeding it with correct and incorrect data to make sure it passes or fails validation accordingly.

As far as database design goes, you can't really verify it with tests - since many designs will work even if they are not ideal (and the definition of ideal changes between different people). Proper database design comes with experience and knowledge, not with automated tests.

  • I see where you are coming from and I fully intend on creating and testing filters that validate data before it even hits the database, but in this question my main intentions were to try verify the design of the database (as much as I can before actually using it) and also verify that what is actually there is working as was intended (for example making sure foreign key constraints aren't broken as @EmmadKareem mentioned in his answer. Thank you for bringing up data validation though as it is a very integral part of any application that uses a database.
    – Kristen D.
    Commented Aug 25, 2012 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.