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
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.