HomePHP Page 3 - Doing More with phpMyAdmin (Part 2)
Total Recall - PHP
In the first segment of this two-part tutorial, I gave you a quick overview of some of the interesting new features available in phpMyAdmin. In this concluding segment, find out how to use phpMyAdmin to define relationships between tables, maintain a log of commonly-used queries and create entity-relationship diagrams.
Before I begin, a quick introduction to the tables I'll be using throughout this tutorial seems to be in order. In order to explain the new features of phpMyAdmin, I'll be using a fictitious company's accounting database, which consists of the following four tables:
The services table: The fictitious system under discussion consists of a service company's accounting database. This company offers customers a number of outsourced services, each of which is associated with a fee and has a unique service ID. This information is stored in a services table, which looks like this:
CREATE TABLE `services` (
`sid` tinyint(4) NOT NULL default '0',
`sname` varchar(255) NOT NULL default '',
`sfee` float(6,2) NOT NULL default '0.00',
PRIMARY KEY (`sid`)
) TYPE=MyISAM;
INSERT INTO `services` VALUES
(1, 'Accounting', '1500.00');
INSERT INTO `services` VALUES
(2, 'Recruitment', '500.00');
INSERT INTO `services` VALUES
(3, 'Data Management', '300.00');
INSERT INTO `services` VALUES
(4, 'Administration', '500.00');
INSERT INTO `services` VALUES
(5, 'Customer Support', '2500.00');
INSERT INTO `services` VALUES
(6, 'Security', '600.00');
The clients table: The company also has a list of its current clients stored in a separate clients table. Each client is identified with a unique customer ID.
CREATE TABLE `clients` (
`cid` tinyint(4) NOT NULL default '0',
`cname` varchar(255) NOT NULL default '',
PRIMARY KEY (`cid`)
) TYPE=MyISAM;
INSERT INTO `clients` VALUES (101, 'JV Real Estate');
INSERT INTO `clients` VALUES (102, 'ABC Talent Agency');
INSERT INTO `clients` VALUES (103, 'DMW Trading');
INSERT INTO `clients` VALUES (104, 'Rabbit Foods Inc');
INSERT INTO `clients` VALUES (110, 'Sharp Eyes Detective Agency');
The branches table: Each customer may have one or more branch offices. The branches table lists the branch offices per customer, together with each branch's location. Each branch has a description, a unique branch ID, and a foreign key reference to the customer ID.
CREATE TABLE `branches` (
`bid` int(8) NOT NULL default '0',
`cid` tinyint(4) NOT NULL default '0',
`bdesc` varchar(255) NOT NULL default '',
`bloc` char(3) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO `branches`
VALUES (1011, 101, 'Corporate HQ', 'CA');
INSERT INTO `branches`
VALUES (1012, 101, 'Accounting Department', 'NY');
INSERT INTO `branches`
VALUES (1013, 101, 'Customer Grievances Department', 'KA');
INSERT INTO `branches`
VALUES (1041, 104, 'Branch Office (East)', 'MA');
INSERT INTO `branches`
VALUES (1042, 104, 'Branch Office (West)', 'CA');
INSERT INTO `branches`
VALUES (1101, 110, 'Head Office', 'CA');
INSERT INTO `branches`
VALUES (1031, 103, 'N Region HO', 'ME');
INSERT INTO `branches`
VALUES (1032, 103, 'NE Region HO', 'CT');
INSERT INTO `branches`
VALUES (1033, 103, 'NW Region HO', 'NY');
The branches_services table: Services supplied to each branch office are listed in this table, which contains pairs of branch IDs and service IDs (foreign keys into the branches and services table respectively).
CREATE TABLE `branches_services` (
`bid` int(8) NOT NULL default '0',
`sid` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `branches_services` VALUES (1011, 1);
INSERT INTO `branches_services` VALUES (1011, 2);
INSERT INTO `branches_services` VALUES (1011, 3);
INSERT INTO `branches_services` VALUES (1011, 6);
INSERT INTO `branches_services` VALUES (1012, 1);
INSERT INTO `branches_services` VALUES (1013, 5);
INSERT INTO `branches_services` VALUES (1041, 1);
INSERT INTO `branches_services` VALUES (1041, 4);
INSERT INTO `branches_services` VALUES (1042, 1);
INSERT INTO `branches_services` VALUES (1042, 6);
INSERT INTO `branches_services` VALUES (1101, 1);
INSERT INTO `branches_services` VALUES (1031, 2);
INSERT INTO `branches_services` VALUES (1031, 3);
INSERT INTO `branches_services` VALUES (1031, 4);
INSERT INTO `branches_services` VALUES (1032, 3);
INSERT INTO `branches_services` VALUES (1033, 4);
PHP 
Doing More With phpMyAdmin (Part 1)
