PhpRiot
Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...


When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Example Database

In the documentation for Zend_Db classes, we use a set of simple tables to illustrate usage of the classes and methods. These example tables could store information for tracking bugs in a software development project. The database contains four tables:

  • accounts stores information about each user of the bug-tracking database.

  • products stores information about each product for which a bug can be logged.

  • bugs stores information about bugs, including that current state of the bug, the person who reported the bug, the person who is assigned to fix the bug, and the person who is assigned to verify the fix.

  • bugs_products stores a relationship between bugs and products. This implements a many-to-many relationship, because a given bug may be relevant to multiple products, and of course a given product can have multiple bugs.

The following SQL data definition language pseudocode describes the tables in this example database. These example tables are used extensively by the automated unit tests for Zend_Db.

CREATE TABLE accounts (
  account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
  product_id        INTEGER NOT NULL PRIMARY KEY,
  product_name      VARCHAR(100)
);

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
);

CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

Also notice that the 'bugs' table contains multiple foreign key references to the 'accounts' table. Each of these foreign keys may reference a different row in the 'accounts' table for a given bug.

The diagram below illustrates the physical data model of the example database.

Zend Framework