CCNC/CCNC Module 5/Designing and creating tables/Table Relationships/Testing By Adding Data
Testing the design
Its a very good idea to test your database designs on a frequent and regular basis as you iterate through their development. One of the first tasks in testing a design in to add, update and delete data to and from the new database tables. When the database has relationships defined with referential integrity rules applied this testing will require some thought. When developing a testing strategy you should consider the following steps;
- Identify which tables are parent tables and which are child tables, and potentially which are grandchild tables. The parent tables contain NO foreign key references, child tables do.
- Design data sets for each parent table. These data sets should contain data for all the mandatory fields.
- Add data to the parent tables and keep reference of any primary key (PK) values you will want to reference as foreign keys (FK).
- Design data sets for each child table. These data sets should contain data for all the mandatory fields and have foreign keys referencing back to primary key values in parent tables.
- Add data sets to the child tables. Try and break this data entry by entering foreign key values that do not exist as primary key values from parent tables.
- Design update data sets, where some of the updates violate the rules assigned to the table(s).
- Make updates to data in the rows of all tables.
- Try to delete records from parent tables with dependent child records in related tables.
- First delete child records then their parent records.
Identifying the tables
In our example database we currently have two parent tables and two child tables. The People and Addresses are the parents and the PhoneNumbers and People_xref_Addresses are the children. Once you have identified the parent and child tables it is time to develop test data.
Designing the test data
Designing the test data begins with the parent tables, this is due to the child tables having dependencies on data values already being in the parent tables. It is a good idea to create a variety of records for the parent tables that test any of the mandatory fields and particular formatting requirements. Formatting requirements refer to date formats (i.e. DD/MM/YY; MM/DD/YYYY; etc.) or Numeric Formats (i.e. 0.00; 0,00; etc.).
Adding parent table data
Adding data to parent tables is easy as all you need is enter values into the tables fields. Due to the table being a parent table there will be no need to reference data in other tables. While testing directly against the parent tables it would be useful to write down the primary key values of the new entries as these values will be used when testing child tables.
Adding child table data
When entering data into child tables new records will have to reference the primary key values of parent tables. These values are known as foreign keys and should be entered into the field in the child table corresponding to the parent record. When an incorrect foreign key value is entered into a child table an error will occur.
Adding cross reference data
In a many-to-many relationship the child table (or cross reference table) contains foreign key values for both the parent tables. As shown in the People_xref_Addresses table values need to be added to the person_id and address_id fields that have corresponding values in the respective People and Addresses tables. Failing to enter values that exist in these two parent tables will cause a PK error similar to the one in the previous step.
Updating the data
When updating data in a record only the non-key fields can be updated. And when making updates these values need to meet any rules applied to the fields of the table having data being updated.
Deleting the data
When deleting data from within a one-to-many or many-to-many relationship the child data needs to be deleted first. Deleting parent data before the child data would orphan the child data.
Test your knowledge